Forum Discussion

Rosh89's avatar
Rosh89
Icon for Helper I rankHelper I
3 years ago
Solved

DAX for calculating previous month values

Hi,   I have a table as shown below: Month Period Customer Bonus Previous bonus Jan-22 1 A 100   Feb-22 2 A 200 100 Mar-22 3 A 150 200 Jan-22 1 B 300   Feb-22...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Rosh89 ,

     

    You could add a date column like below:

    date = DATEVALUE("1-"&'Table'[Month])

    Then create a measure:

    Measure = CALCULATE(SUM('Table'[Bonus]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[date]=EDATE(SELECTEDVALUE('Table'[date]),-1)))

     

     

bsheffer's avatar
bsheffer
Icon for Continued Contributor rankContinued Contributor
3 years ago

why not create a date in your calendar table using date(yyyy, mmm, 01) as First_of_month for each month and then you can use date functions to get the prior month?

 

is the month a text string?

Rosh89's avatar
Rosh89
Icon for Helper I rankHelper I
3 years ago

bsheffer  In this table month is a text. I tried what you mentioned about using date as first of every month but it does not work that way because you need a calendar table marked as a date table with continous dates without any gap. Without a calendar table containing continous dates the date functions dont work. 

  • bsheffer's avatar
    bsheffer
    Icon for Continued Contributor rankContinued Contributor
    3 years ago

    if you want to use the calendar table (which doesn't have to be marked as a date table), you can use many available code snippets to create it. 

    Then you have to create a column in your fact table that converts your month, year field [Month] into a date (which is why I suggested date(yyyy, mm, 01) where yyyy is like right('fact'[month], 4) and mm is probably a switch statement). 

    That date can be linked to the calender table and now you can use all the date functions like adddate() on that table to get your monthly totals for whatever month range you want.