This month billing using a (Card)

DAX DAX Calculations
1

This should be easy, but I’m not able to get it aligned.
On the first page the dashboard, there are three Cards for for Fiscal YTD and same period last year, then right below it there is Month over Month. It’s the month over month and % of this that i can’t get right.

I need a DAX that simply sums up the current months total billing and gives it back as a single measure. As well i would like to add some formatting so that when it goes up month over month there would be some sort of RED color or notification.

My advanced date table should make this easy, but i seem to struggle using it.

TimeSheet Data V2.pbix (32.1 MB)

2

@chad.sharpe,

What are the values you expect to see in the bottom three cards?

  • Brian
1 Like
3

@chad.sharpe,

Give this a go:

MTD Current FY =

VAR MaxDate =
    CALCULATE(
        MAX( 'Advanced Date Table'[Date] ),
        REMOVEFILTERS('Advanced Date Table'[Date]),
        FILTER( 'Time Sheets', [Total Billings $] <> BLANK() )
    )
VAR MaxMonth =
    MONTH( MaxDate )
VAR MaxYr =
    YEAR( MaxDate )
VAR MinDate =
    DATE( MaxYr, MaxMonth, 1 )
VAR FiscalYear =
    MAX( 'Advanced Date Table'[Fiscal Year] )
VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE(
            ALL( 'Time Sheets' ),
            'Advanced Date Table'[Date],
            'Advanced Date Table'[Fiscal Year]
        ),
        "@Billings", [Total Billings $]
    )
VAR Result =
    SUMX(
        FILTER(
            vTable,
            'Advanced Date Table'[Date] <= MaxDate
                && 'Advanced Date Table'[Date] >= MinDate
        ),
        [@Billings]
    )
RETURN
    Result

Previous month is just a slight variation of the above using EOMONTH to move the date from the above measure back a month.

image

Nice-looking dashboard by the way.

I hope this is helpful. Full solution file attached below.

– Brian
eDNA forum – TimeSheet Data V2 solution.pbix (32.1 MB)

2 Likes
4

This works perfect…
Thanks

5

Your below answer was spot on…