Plotting Cumulative Financial Year to Date by Month

[Anonymous]

Hi everyone,

I'm very new to Power BI so please excuse my lack of ability here.

I am working with sales data (SaleDate, SalesAmount, SalesFTYD), and I have also created a DimDate table (Date, Month, Month_Numeric, Year, Year-Month). 

SalesFYTD is a calculated column (   = TOTALYTD(SUMX(SalesData, SalesData[SalesAmount), DimDate[Date], "06/30")   ). 

I wish to graph my data in a histogram such as the photo attached but so that when I select a particular month as a filter the plot will change to show cumulative financial year totals up until the month I've selected. Eg. If December is selected the plot will show July, August, September, October, November, December... If September is selected the plot will show July, August, September... 

At the moment when I select a filter it displays only the selected month, not the previous months within said financial year. 

Also, if the plot could be ordered to show July -> June instead of January -> December that would be excellent!

Is this possible? If so, how? 

Thanks in advance for all your help!

Nicole 🙂

[v-jiascu-msft]

Hi @Anonymous ,

Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

Please download a demo from the attachment. I think you should use financial year and month instead.

Result =
VAR selectedFinancialMonth =
    IF (
        CALCULATE (
            SELECTEDVALUE ( DimDate[FinancialMonth] ),
            ALLEXCEPT ( DimDate, DimDate[FinancialMonth] )
        )
            <> BLANK (),
        CALCULATE (
            MAX ( DimDate[Date] ),
            ALLEXCEPT ( DimDate, DimDate[FinancialMonth], DimDate[FianacialYear] )
        ),
        CALCULATE ( MAX ( DimDate[Date] ), ALL ( DimDate ) )
    )
RETURN
    CALCULATE (
        IF (
            MAX ( DimDate[date] ) > selectedFinancialMonth,
            BLANK (),
            TOTALYTD ( SUM ( FactSales[SalesQuantity] ), 'DimDate'[Date], "6-30" )
        ),
        ALL ( DimDate[FinancialMonth] )
    )

Best Regards,

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

Hi Ashish,

Not really what I'm looking for but thanks for trying for me! If anyone else has anything to contribute I'm happy to try it!
Nicole 🙂