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 guys,
I have a simple measure based on two other measures.
Year | Quarter | Month | Measures |
2019 | Qtr 3 | July | 857.46 |
2019 | Qtr 3 | August | 1502.83 |
2019 | Qtr 3 | September | 2143.54 |
2019 | Qtr 4 | October | 2819.25 |
2019 | Qtr 4 | November | 3484.40 |
2019 | Qtr 4 | December | 4254.06 |
Can you help me on this?
THank you!
Solved! Go to Solution.
Hi @akbjf ,
You could refer to my sample for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@akbjf First remove date hierarchy
then create a measure:
Measure2 = SUMX (
FILTER (
ALLSELECTED ( data1[Date] ),
data1[Date] <= MAX ( data1[Date] )
),
[Measure]
)
@akbjf ,
TRy the below measure:
@akbjf , you can done with help from a date calendar
Example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Try this:
CumulativeSum =
CALCULATE(
[Measures)],
FILTER(
ALLSELECTED('YourTable'[Month]),
ISONORAFTER('YourTable' [Month], MAX('YourTable' [Month]), DESC)
)
)
@akbjf ,
Try the below DAX for cumlative sum:
MEASURE = CALCULATE(( [PR % FC] * [Vol. MSU FC] ),FILTER(ALL(Table),Table[DateCOlumn]<=MAX(Table[DateCOlumn])))
Hi @akbjf
Try something like.
Measure =
VAR __maxDate = MAX( Dates[Date] )
RETURN
CALCULATE(
SUMX( VALUES( dates[YearMonthColumn] ), [PR % FC] * [Vol. MSU FC] ),
ALL( Dates ),
Dates[Date] <= __maxDate
)
Hi Mariusz!
THank you for your suggestion.
I tried your formula, but it only accummulates at the end of series.
Year | Quarter | Month | Measures | Measure Mariusz | Should be like this |
2019 | Qtr 3 | July | 857.5 | 857.50 | 857.50 |
2019 | Qtr 3 | August | 645.4 | 645.40 | 1502.90 |
2019 | Qtr 3 | September | 640.71 | 640.71 | 2143.62 |
2019 | Qtr 4 | October | 675.69 | 675.69 | 2819.31 |
2019 | Qtr 4 | November | 665.13 | 665.13 | 3484.44 |
2019 | Qtr 4 | December | 769.63 | 769.63 | 4254.08 |
2020 | Qtr 1 | January | 743.95 | 743.95 | 4998.03 |
2020 | Qtr 1 | February | 728.58 | 728.58 | 5726.60 |
2020 | Qtr 1 | March | 832.41 | 832.41 | 6559.01 |
2020 | Qtr 2 | April | 802.21 | 802.21 | 7361.22 |
2020 | Qtr 2 | May | 459.49 | 459.49 | 7820.72 |
2020 | Qtr 2 | June | 0.00 | 7820.72 | |
7820.72 |
Can we make it accummulate on each row like shown on the column above?
Thank you1
Please create a measure like this and try:
CumulativeSum =
CALCULATE(
[Measures)],
FILTER(
ALLSELECTED('YourTable'[Month]),
ISONORAFTER('YourTable' [Month], MAX('YourTable' [Month]), DESC)
)
)
Hi @vin26 ,
Thank you for your suggestion!
I tried to use your formula, it yielded just the same like my original formula:
Below is the code:
Measure Vin26 = CALCULATE([Measures],
FILTER(
ALLSELECTED(Consolidated[Date].[Month]),
ISONORAFTER(Consolidated[Date].[Month], MAX(Consolidated[Date].[Month]), DESC)
)
)
Something wrong with my formula?
Thank you!
@Anonymous as a quick solution please create a new column for month
@akbjf ,
Try my given formula and you'll get your expected answer:
@akbjf ,
Again posting for your reference:
Hi @Tahreem24 ,
Thank you for your suggestion! Sorry just got to try your formula. In my PBI it did not add up unlike yours.
How did you make it work? I think I copied your formula template just right:
MEASURE TAHREEM = CALCULATE(([Measures]),FILTER(ALL(Consolidated),Consolidated[Date]<=MAX(Consolidated[Date])))
Thank you!
@akbjf ,
I have replicated your sample at my side and it works seemlessly fine at my side:
If possible so can you attached your excel or PBIX by masking any confidential data.
Hi @Tahreem24 and everyone,
I think you can make it work because you made the "Measure" a column, not a measure.
Here's the link to the PBI with data model exactly matched with this case.
Thank you!
@akbjf First remove date hierarchy
then create a measure:
Measure2 = SUMX (
FILTER (
ALLSELECTED ( data1[Date] ),
data1[Date] <= MAX ( data1[Date] )
),
[Measure]
)
@akbjf ,
TRy the below measure:
Hi @akbjf ,
You could refer to my sample for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zhi,
Thanks for the solution, it works perfectly!
Anyway, is there a way so that the cumulative is reset to 0 when it reaches new fiscal year?
Fiscal year --> start July 1st - June 30th
Thank you!
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |