YTD with dynamic month selection

[MelStaunton]

Hi all,

even though there are various related & solved topics posted, I am not able to apply any of the solutions to my problem. Please point me in the right direction!

I have below data table and want to caluclate YTD (Planned Sales) based on the period selection by the user. If Period 7 is selected, sum all for periods 1-7, if 6 is selected sum all for periods 1-6.

There are other slicers available: Currency & Entity

Year	Period	Currency	Entity	Planned Sales	MonthEndDate
2020	1	EUR	A	10	31-Jan-2020
2020	1	MYR	A	45	31-Jan-2020
2020	2	EUR	A	20	29-Feb-2020
2020	2	MYR	A	90	29-Feb-2020
2020	1	EUR	B	10	31-Jan-2020
2020	1	CNY	B	70	31-Jan-2020
2020	2	EUR	B	20	29-Feb-2020
2020	2	CNY	B	140	29-Feb-2020

I tried the following:

Planned Sales YTD =

VAR MinDate = DATE(YEAR(MAX('Data'[MonthEndDate])),1,1)

VAR MaxDate = SELECTEDVALUE('Data'[MonthEndDate])

RETURN CALCULATE(SUM('Data'[Planned sales]), FILTER('Data', 'Data'[MonthEndDate]>=MinDate && 'Data'[MonthEndDate]<=MaxDate))

or:  RETURN TOTALYTD(SUM('Data'[Planned sales]), 'Data'[MonthEndDate], 'Data'[MonthEndDate]>=MinDate && 'Data'[MonthEndDate]<=MaxDate)

Both return the same number, but for the selected period only, not the total. What am I missing? The Variables return the correct Month selection.

I do have a Date Table, that MonthEndDate has a relationship with.

[dedelman_clng · Accepted solution]

Hi @MelStaunton 

You need to apply something that removes filters from the "Data" table when doing your CALCULATE (I named my table "Planned Sales")

Planned Sales YTD =
VAR MinDate =
    DATE ( YEAR ( MAX ( 'Planned Sales'[MonthEndDate] ) ), 1, 1 )
VAR MaxDate =
    SELECTEDVALUE ( 'Planned Sales'[MonthEndDate] )
RETURN
    CALCULATE (
        SUM ( 'Planned Sales'[Planned Sales] ),
        FILTER (
            ALLEXCEPT (
                'Planned Sales',
                'Planned Sales'[Currency],
                'Planned Sales'[Entity]
            ),
            'Planned Sales'[MonthEndDate] >= MinDate
                && 'Planned Sales'[MonthEndDate] <= MaxDate
        )
    )

You didn't say at what granularity you wanted the YTD total, so modify ALLEXCEPT as needed (or switch to ALL if no other filters are wanted).

All that being said, if you want to use Time Intelligence functions (TOTALYTD, etc), then the dates you reference should be from your date table, not the fact table.

Hope this helps

David 

[MelStaunton]

Sorry @dedelman_clng

I didn't understand that the issue I have with the donut chart, is an underlying one which didn't show in the sample data (which I have amended).

Not every entity has an entry for each Industry every month. So while I theoretically get correct numbers with the below formula, only values that have an entry in the selected period actually show (if period 7 is selected, numbers add up correctly but only industries that have a value in period 7 show). I only noticed once I started filtering entities.....

Any ideas are much appreciated!

Planned Sales YTD Industry =
VAR MinDate =
    DATE ( YEAR ( MAX ( 'POC Data'[MonthEndDate] ) ), 1, 1 )
VAR MaxDate =
    SELECTEDVALUE ( 'POC Data'[MonthEndDate] )
RETURN
    CALCULATE (
        SUM ( 'POC Data'[Planned sales volume] ) / 1000,
        FILTER (
            ALLEXCEPT (
                'POC Data',
                'POC Data'[Currency],
                'POC Data'[LE],
                'POC Data'[ProjectType2],
                'POC Data'[Project category]
            ),
            'POC Data'[MonthEndDate] >= MinDate
                && 'POC Data'[MonthEndDate] <= MaxDate
        ),
        GROUPBY ( 'POC Data', 'POC Data'[Customer Industry] )
    )

Year	Period	Currency	Entity	Planned Sales	MonthEndDate	Industry
2020	1	EUR	A	10	31-Jan-2020	Mobility
2020	1	MYR	A	45	31-Jan-2020	Mobility
2020	2	EUR	A	20	29-Feb-2020	Energy
2020	2	MYR	A	90	29-Feb-2020	Energy
2020	1	EUR	B	10	31-Jan-2020	Mobility
2020	1	CNY	B	70	31-Jan-2020	Mobility
2020	2	EUR	B	20	29-Feb-2020	Medical
2020	2	CNY	B	140	29-Feb-2020	Medical

[dedelman_clng]

@MelStaunton are you saying that you want an industry to be represented as 0 if it has no records in that period? For that you can put "+0" at the very tail end of your calculate code.  ETA: Although I don't think that zeroes would show up on donut/pie charts - they would, however, on table/matrix and usually line charts.

If that's not what you are saying, can you please elaborate or mock up what you are trying to do with respect to industries?

David

[dedelman_clng · Accepted solution]

Hi @MelStaunton  - 

After looking through your sample report, you're going to need a separate date table to do these calculations instead of just trying to use the dates on the fact table.  The advantage to this is that you can start using functions like TOTALYTD instead of having to "build" that function from scratch.

Also, just for the sake of completeness, on the donut chart you should use the "Dimension" industry instead of the industry value from the fact table. In neither case will this show a 0 on the donut chart itself, but all of the industries will show up on the legend.

I have attached your sample report with a second page that uses the DateTab date table to run the same calculations.

Hope this helps

David

[amitchandak]

@MelStaunton , as long as the period is coming from the date table, you should be able to use datesytd /time intelligence with date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) // Changed based on FYLast YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

1.Creating Financial Calendar
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[dedelman_clng · Accepted solution]

Hi @MelStaunton 

You need to apply something that removes filters from the "Data" table when doing your CALCULATE (I named my table "Planned Sales")

Planned Sales YTD =
VAR MinDate =
    DATE ( YEAR ( MAX ( 'Planned Sales'[MonthEndDate] ) ), 1, 1 )
VAR MaxDate =
    SELECTEDVALUE ( 'Planned Sales'[MonthEndDate] )
RETURN
    CALCULATE (
        SUM ( 'Planned Sales'[Planned Sales] ),
        FILTER (
            ALLEXCEPT (
                'Planned Sales',
                'Planned Sales'[Currency],
                'Planned Sales'[Entity]
            ),
            'Planned Sales'[MonthEndDate] >= MinDate
                && 'Planned Sales'[MonthEndDate] <= MaxDate
        )
    )

You didn't say at what granularity you wanted the YTD total, so modify ALLEXCEPT as needed (or switch to ALL if no other filters are wanted).

All that being said, if you want to use Time Intelligence functions (TOTALYTD, etc), then the dates you reference should be from your date table, not the fact table.

Hope this helps

David 

[MelStaunton]

@dedelman_clng Great, thank you! I adjusted my ALLEXCEPT and now get the correct values in a table.

Planned Sales YTD = 

VAR MinDate = DATE(YEAR(MAX('POC Data'[MonthEndDate])),1,1)

VAR MaxDate = SELECTEDVALUE('POC Data'[MonthEndDate])

RETURN CALCULATE(SUM('POC Data'[Planned sales volume]), FILTER(ALLEXCEPT('Data', 'Data'[Currency], 'Data'[LE], 'Data'[ProjectType2], 'Data'[Project category]), 'POC Data'[MonthEndDate]>=MinDate && 'POC Data'[MonthEndDate]<=MaxDate))

Follow on question: I'd like to display those values grouped by "industry" and "cluster" in a donut chart. Where do I put the group by?

[dedelman_clng]

If I understand your followup question correctly, "industry" and "cluster" go in the Legend section of the donut chart. Planned Sales YTD is the Value.