Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi,
Try this
NewMeasure = CALCULATE ([Your Measure], DATESBETWEEN(CALENDAR[Date],MINX(ALL(Calendar[Date]),Calendar[Date]),MIN(CALENDAR[Date])-1))
Hope this helps.
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))
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
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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |