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
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.