Cumulative Total rolling over fiscal years

[Anonymous]

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:

• I have a seperate DimDate table used to account for months with no awards
• Fiscal Year is Oct - Sept

Thank you in advance!

[v-zhenbw-msft]

Hi @Anonymous ,

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.

[Anonymous]

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!

[amitchandak]

@Anonymous , 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-Calendar-1-5-Power/ba-p/1187441

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

[Ashish_Mathur · Accepted 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.

[Anonymous]

Hi @Ashish_Mathur - 

This worked. Thanks!

[Ashish_Mathur]

You are welcome.

[pranit828]

HI @Anonymous 

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 ) 
)
)