topic Re: Last 7/14/30 with Same Days Dax Measure for Visual Filter in DAX Commands and Tips

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

cn4422 — Thu, 08 Aug 2024 13:59:43 GMT

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! 🙂

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

aduguid — Fri, 09 Aug 2024 03:23:56 GMT

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

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

cn4422 — Fri, 09 Aug 2024 06:15:27 GMT

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 ^^)

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

aduguid — Fri, 09 Aug 2024 08:04:25 GMT

Try this measure

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

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

cn4422 — Fri, 09 Aug 2024 08:09:15 GMT

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

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

aduguid — Fri, 09 Aug 2024 08:16:07 GMT

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

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

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

cn4422 — Fri, 09 Aug 2024 09:45:08 GMT

Thanks again for your help, much appreciated! 😍