Forum Discussion

dilbagsidhu's avatar
dilbagsidhu
Frequent Visitor
2 years ago

month to month calculation

hello fellows,

im trying to calculate Month to month expenses change in my dataset, which inculdes one whole year data of expenses for different categories on different dates.


i tried finding previous month expenses using below formula, so that i can finally find month to month change.

BUT im making mistake somehwere.

 

Previous month =
CALCULATE([Total],PREVIOUSMONTH('2024'[Date]))


previousMonth2 =
CALCULATE(SUM('2024'[Amount]),DATEADD('2024'[Date].[Date],-2,MONTH))
 
 
but im not able to getany results, 

3 Replies

  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    2 years ago

    Hi dilbagsidhu 

     

    It looks like you are using the fact table in the PREVIOUSMONTH function? And it should be the same table as the slicer, right?

     

    Since the slicer selects May, then the PREVIOUSMONTH function returns the date of the previous month, but since the slicer restricts the month to May, the intersection of the two will be null, leading to a null final result.

     

    You should create a date table and use the dates from the date table in the PREVIOUSMONTH function. But if you want to keep using the fact table that's fine, just remove the month filter of slicer. You can try below measure.

     

    Previous month =
CALCULATE (
    [Total],
    PREVIOUSMONTH ( '2024'[Date] ),
    ALL ( '2024'[Month Field of Slicer] )
)

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

  • dilbagsidhu's avatar
    dilbagsidhu
    Frequent Visitor
    2 years ago

    xifeng_L hello, thankyou,  i  created new date table for slicer, and used this measure:

    Previous month =
    CALCULATE([Total],PREVIOUSMONTH('2024'[Date]),ALL('date'[month]))

    now im getting previous month value but shows total value for all categories and subcategories in matrix.
    • xifeng_L's avatar
      xifeng_L
      Icon for Super User rankSuper User
      2 years ago

      Need more info to judge, can you provide the pbix file?