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 could able to achieve sum of ll the months.
Provided the example for "sum" of all the columns and we are showing that in right hand side.
Is it possible to have running total? Provided the sample output below
Thanks in Advance
Prakash
Solved! Go to Solution.
Hi @Anonymous
Try something like this.
Sales RT =
IF(
NOT ISEMPTY( 'Table' ),
VAR __maxDate = MAX( 'Calendar'[Date] )
RETURN
CALCULATE(
[Sales],
'Calendar'[Date] <= __maxDate,
ALL( 'Calendar' )
)
)
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario.
Table:
Then you may create a calculated table and build a one-to-many relationship between two tables as follows.
Date Table = CALENDARAUTO()
You can create a measure as below.
Finally, you may use matrix visual to show the reslut:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Try something like this.
Sales RT =
IF(
NOT ISEMPTY( 'Table' ),
VAR __maxDate = MAX( 'Calendar'[Date] )
RETURN
CALCULATE(
[Sales],
'Calendar'[Date] <= __maxDate,
ALL( 'Calendar' )
)
)
Hi @Mariusz ,
Can we achieve this without the help of calendar table?
Thanks.
Also any help n the below would be helpful
Hi @Anonymous
You can replace Calendar Date column with a date column Comming from your table, but using a Date Dimension / Calendar Table is considered the best practice.
Hi @Mariusz
I changed as per your suggestion but it is not working.
Sales RT =
IF(
NOT ISEMPTY( 'Table' ),
VAR __maxDate = MAX( 'Table'[Date] )
RETURN
CALCULATE(
[Sales],
'Table'[Date] <= __maxDate,
ALL( 'Table' )
)
)
The counts are not matching.
Thanks
Hi,
@Mariusz Thanks for your help.
Actually i forgot to put AllExcept. Now it is woking fine
Sales RT =
IF(
NOT ISEMPTY( 'Table' ),
VAR __maxDate = MAX( 'Table'[Date] )
RETURN
CALCULATE(
[Sales],
'Table'[Date] <= __maxDate,
ALLExcept( 'Table','Table'[Source System] )
)
)
Hi @Mariusz
I would like to have a rollin 3 months average. will the below code is good? or do we have any better way?
Sales RT =
IF(
NOT ISEMPTY( 'Table' ),
VAR __maxDate = MAX( 'Table'[Date] )
VAR __minDate = MIN( 'Table'[Date] )-90
RETURN
CALCULATE(
[Sales],
'Table'[Date] <= __maxDate,'Table'[Date] >= __minDate,
ALLExcept( 'Table','Table'[Source System] )
)
)
Thanks
If you create a running total over month and add that to matrix it should work ?
Please refer
https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115
https://www.daxpatterns.com/cumulative-total/
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |