Last 7/14/30 with Same Days Dax Measure for Visual Filter

[cn4422]

Hi,

I want to create a DAX measure for simple dates, like "last 7/14/30" days and a "same period as last 7/14/30 days before" that I can then use as a filter for visuals.

So basically I have one visual that should have a date range "last 7 days" (for this I wouldn't need a DAX measure) and I have the same visual a second time that has "last 14-7 days", to get a comparison.

I have already created a DateTable 'Datum[Date]' to which the date periode should reference (since I have multiple relationships with the DateTable-->Other tables).

I'm sure this is pretty easy, but I somehow don't get it.
I can do calculations with time periods included and stuff, but somehow I'm not able to accomplish a simple time period...

Thx for your help! 🙂

[aduguid · Accepted solution]

I think this may be what you're after. All you have to do is update the date ranges.

Calendar Ageing = 
VAR _today_date =       TODAY()
VAR _min_date =         DATE( YEAR( _today_date ) - 6, 01, 01 )

VAR _result = 
UNION (
      ADDCOLUMNS (CALENDAR ( _today_date - 7, _today_date),         "Ageing Days", "0 to 7",    "Ageing Days Order", 1)
    , ADDCOLUMNS (CALENDAR ( _today_date - 14, _today_date - 8 ),   "Ageing Days", "8 to 14",   "Ageing Days Order", 2)
    , ADDCOLUMNS (CALENDAR ( _today_date - 30, _today_date - 15 ),  "Ageing Days", "15 to 30",  "Ageing Days Order", 3)
    , ADDCOLUMNS (CALENDAR ( _min_date, _today_date - 31),          "Ageing Days", "30+",       "Ageing Days Order", 4)
)

RETURN
_result

Then you can set the order of the label to the sort order column

After that, you can update the relationship to the calendar table

Then any metric you have associated to the calendar table can use the ageing table

[cn4422]

Hi,

cool, thanks for your answer, I will try this out! 👌

Another quick question: Is there a simple way to build a measure that shows for example the last 7 days?

Something like:

Last 7 days =

Datum[Date], Today, -7, day

(please excuse my poor DAX understanding ^^)

[aduguid]

Try this measure 

Last 7 Days Measure =
CALCULATE(
    SUM('YourTable'[YourValueColumn]), 
    DATESINPERIOD(
        'Datum'[Date], 
        MAX('Datum'[Date]), 
        -7, 
        DAY
    )
)

[cn4422]

Ok - and is this possible without the "SUM-Part"? 
So that the output of the measure is only the date period (last 7 days?)

[aduguid]

If you want to return the last 7 days as dates.  Try this DAX query.

Last 7 Days = CALENDAR(TODAY() - 7, TODAY())

[cn4422]

Thanks again for your help, much appreciated! 😍