Dax question on last 30 days/year

[jondeck24]

Hey Guys,

I have a rolling set of data that will be constantly updated.  I need to create a visual that will allow me to show the number of transactions for the last 30 days and then for the last 12 months, by month.  So my two visuals will be bar charts and will have 30 bars for the last 30 days and obviously 12 for the last 12 months. 

I'm unable to figure out how to write this in DAX.  I could obviously filter but this needs to be a rolling report that auto-updates when it opens meaning the people who use it won't want to filter out the dates each time.  I tried DatesInPeriod but I keep getting an error saying its getting more than 1 value for the date when it was only expecting 1.  This kind of makes sense as I could have had say 30 transactions today, 25 yesterday, and so on so it would be getting 25/30 hits for that one date.  I also tried Now() - 30 but that just gave me one date 30 days ago, lol.

What would be the formula/function to do this?   Thanks for your help!

[Greg_Deckler · Accepted solution]

Have a look here:

http://www.daxpatterns.com/time-patterns/

I would also think that some of the Time Intelligence functions:

https://support.office.com/en-US/article/Time-Intelligence-Functions-DAX-0571dcda-e4e8-42a8-b205-e1f...

Like SAMEPERIODLASTYEAR, PARALLELPERIOD and PREVIOUSYEAR might get you there.

[desertislesql]

You may have problems getting DAX Time Date Calculations in Power BI Designer since there is no way to mark a Date Table as date.  That means that SamePeriodLastYear won't work.  Fortunately, since the 8-20 release there is a work around. I wrote a how to on my blog http://wp.me/p4beUo-df

[michaelamadi]

The time intelligence calculations will work without marking a table as a date table so long as date columns are used to join the fact to the date table. Marking a table as a date table in order to enable time intelligence functions to work should only be necessary if the fact table is linked to the date table via an integer key or any other non-date data type.

[AlexGorev]

@michaelamadi is correct that marking a table as Date Table only allows some UI optimizations in the Power Pivot and is not required for time intelligence functions to work.

Check my reply in the thread below for more details on what is needed:

http://community.powerbi.com/t5/Desktop/How-to-mark-Date-table/m-p/3291#M619

Hope this helps!

Alex.