Forum Discussion

mcornfield's avatar
mcornfield
Helper III
4 years ago

DAX Calculation for Prior Period Date Range

Period Column is broken up into 2 month increments. I need to compare values vs the previous period and am not sure the DAX Formula. Below is what I have in PBI and then what I am looking to create. ...
Need Help
Show and Tell
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
4 years ago

mcornfield , if you have date range preferred from a joined date table

 

same period based on date range
Last Period =
var _max =maxx(date,date[date])
var _min =maxx(date,date[date])
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]<=_maxX &&date[date]>=_minX))

 

 

or refer

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

  • mcornfield's avatar
    mcornfield
    Helper III
    4 years ago

    Thanks, So Everything is in one table. the columns I have are Date, Year, Month Period, Period Number, and # Acheived.   SO I am not sure how to implement your suggestion. 

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      mcornfield , prefer to have date table, "all" does not go fine when what do display row level data.

       

      You can replace the date table with your table and check

       

      To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

      • mcornfield's avatar
        mcornfield
        Helper III
        4 years ago

        Thank you, the numbers look way high. I am wondering if it's not adhering to the slicers I have.

        Any thoughts would be appreciated 🙂