Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION

@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

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlB
Super User
Super User

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
Not applicable

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!

@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

Anonymous
Not applicable

@v-yuta-msft

 

Thanks for your reply it works!! I'll accept as a solution, but please help me one more thing..
What if I want to calculate amount until the end of the last december. So on the slicer I chose one month, than the calculation should be until the end of last year december!!

Thank you in advance for your help!!

Máté

@Anonymous,

 

You may try measure below:

 

Result =
VAR Selected_Year =
YEAR ( SELECTEDVALUE ( Table[Date] ) )
RETURN
CALCULATE (
SUM ( Table[Value] ),
FILTER ( Table, YEAR ( Table[Date] ) <= Selected_Year - 1 )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous

 

Ah ok. Then try:

 

NewMeasure =
CALCULATE (
    [Your Measure],
    FILTER ( ALL ( 'Date' ), 'Date'[Date] < MIN ( 'Date'[Date] ) )
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.