Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate until last month

Hi All,

 

I'd like to create a Balance Sheet, where the first column would be the total amount until previous month using a filter selection with any month. 

For example: If a filter the page by the month of January-2019, the total amount I'd like to see in the first column is the total amount ever until December-2018. But it always change as I change the month filter.

Can anybody help how I can create this total amount column (measure) until last month?

 

Thank you in advance for your help!

Mate Kiss

Need Help
Show and Tell
Tips and Tricks
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous,

     

    No sample data, so suppose the slicer and amount data is in same table, to be general, you may also try measure below:

    Result =
VAR Selected_Month =
    MONTH ( SELECTEDVALUE ( Table[Date] ) )
RETURN
    CALCULATE (
        SUM ( Table[value] ),
        FILTER ( Table, MONTH ( Table[Date] ) <= Selected_Month - 1 )
    )

    Regards,

    Jimmy Tao

7 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous

    You can use DATEADD with a Date table related to your fact table to have your measure compute over the previous month. Something like:

     

    NewMeasure = CALCULATE ([Your Measure], DATEADD('Date'[Date],-1,MONTH))

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for your reply!

      I understand, but it shows only the previous month, but what I excatly need is that the total amount before the selected month, not only the previous but all of the previous months amounts!

      Thanks!

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous

         

        Ah ok. Then try:

         

        NewMeasure =
CALCULATE (
    [Your Measure],
    FILTER ( ALL ( 'Date' ), 'Date'[Date] < MIN ( 'Date'[Date] ) )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    Try this

     

    NewMeasure = CALCULATE ([Your Measure], DATESBETWEEN(CALENDAR[Date],MINX(ALL(Calendar[Date]),Calendar[Date]),MIN(CALENDAR[Date])-1))

     

    Hope this helps.