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,
I have a dataset with opportunities awarded and rejected and the amount for a three year period. I want to be able to visualize on a area chart the 3 fiscal years and the cumulative total for each of those years. I'm having trouble as my measure has the cumulative total roll over from each fiscal year instead of resetting.
This my measure for awarded opportunities:
Amount_Awarded = CALCULATE(
SUM('GC Trends'[Amount.amount]),
FILTER('GC Trends', 'GC Trends'[Stage] = "Awarded")
)
This is my cumulative total awarded measure:
Cumulative_Awarded =
CALCULATE([Amount_Awarded],
FILTER(
ALL(DimDate), DimDate[Date] <= max(DimDate[Date])))
As you can see when I use the line graph this is what I get:
As you can see the end of FY17 48million is the start of FY18 and the end of FY18 is the start of FY19.
I've tried changing the cumulative measure filter from "all" to "allselected" with this code:
Cumulative_Awarded =
CALCULATE([Amount_Awarded],
FILTER(
ALLSELECTED(DimDate), DimDate[Date] <= max(DimDate[Date])))
This change appears to work but only if I have a filter for the fiscal years.
How do I achieve a stacked line graph with each starting of the fiscal years starting from the first value in that fiscal year?
Notes:
Thank you in advance!
Solved! Go to Solution.
Hi,
Does this measure work?
Cumulative_Awarded = CALCULATE([Amount_Awarded],DATESYTD(DimDate[Date],"30/9"))
Ensure that in all your visuals/slicers, you drag any time dimension (Year/Month etc/) from the DimDate Table. Also, ensure that the relationship is set well (Many to One > SIngle).
If it does not help, then share the link from where i can download your PBI file.
Hope this helps.
Hi @ttseng ,
Do you have a FY column in DimDate table?
If yes, maybe you can try this measure, calculate the cumulative value in the same fiscal year.
Cumulative_Awarded =
CALCULATE (
[Amount_Awarded],
FILTER (
ALL ( DimDate ),
DimDate[Date] <= MAX ( DimDate[Date] )
&& DimDate[FY] = MAX ( DimDate[FY] )
)
)
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-zhenbw-msft -
I tried the solution posted first and it worked but also tried your solution and was able to do get the same results. This is to say that they both work! Thanks!
@ttseng , what you did should work contineous cumulative
Cumulative_Awarded =
CALCULATE([Amount_Awarded],
FILTER(
ALLSELECTED(DimDate), DimDate[Date] <= max(DimDate[Date])))
For only YTD try
YTD Amount_Awarded= CALCULATE(SUM([Amount_Awarded]),DATESYTD('Date'[Date],"9/30"))
Last YTD Amount_Awarded= CALCULATE((Sales[Amount_Awarded]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))
Create FY Calendar : https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Hi,
Does this measure work?
Cumulative_Awarded = CALCULATE([Amount_Awarded],DATESYTD(DimDate[Date],"30/9"))
Ensure that in all your visuals/slicers, you drag any time dimension (Year/Month etc/) from the DimDate Table. Also, ensure that the relationship is set well (Many to One > SIngle).
If it does not help, then share the link from where i can download your PBI file.
Hope this helps.
You are welcome.
HI @ttseng
In DAX, you can specify the year end date in TOTALYTD() function.
YTD =TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey], ALL('DateTime'), "6/30")
For last year YTD, you can add SAMEPERIODLASTYEAR() in CALCULATE().
LYTD = CALCULATE ( TOTALYTD ( SUM ( InternetSales_USD[SalesAmount_USD] ), DateTime[DateKey], ALL ( 'DateTime' ), "6/30" ), SAMEPERIODLASTYEAR ( DateTime[DateKey] ) )
Or
LYTD = VAR DataMaxDate = CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ) RETURN CALCULATE ( [YTD], SAMEPERIODLASTYEAR ( DATESBETWEEN ( Date[Date], BLANK (), DataMaxDate ) ) )
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |