Power BI YTD calculation in montly bases

Question

i have a problem regarding visualisation of YTD average.

I have a database in excel which is linked to Power BI. The database is the following format: database structure What is needed to be plotted is the montly average and the YTD average based on the data. The Power BI report has some slicers, so 5-6 different regions, and 5-6 different model types to be selected.

Montly average is working with theshowing average of leadtimes per each period. Period is mainly a month, but not in the same system as a calendar month. P01 is October, P02 is November, P03 is December.... P12 is September.

So what is needed to be calculated is the YTD average where the slicers are still working:

For P01 the monthly and YTD average is the same. For P02 the montly average is based on all item reported for P02 but the YTD average is the following: YTD P02 = Sum of the leadtimes divided by sum of the quantity in periods of P01 and P02 together.

For P03 the YTD whould be Sum of leadtimes in period P01-P03 divided by the quantity of items in the same period.

etc. for P04-P12 ongoing the same setup.

I'm using power Bi to visualate the monthly average, and also the monthly quantity (count) can be calculated.

How could i manage a graph where the ytd would be using the calculated values of power bi? power bi graph

The monthly average and montly quantity exist, so the task to write a function using the following values:

YTD P01= AVG Leadtime P01/ count of leadtime P01
YTD P02= (AVG P01*Count P01 + AVG P02*count P02 ) / (Count P01 + count P02)
YTD P03= (AVG P01*Count P01 + AVG P02*count P02 + AVG P03*Count P03) / (Count P01 + count P02 + count P03)

.... etc until P12.

Do anyone has an idea for the solution? Thanks in advance.

Gabor

This may be a good starting point for you: daxpatterns.com/time-patterns — BarneyL

BarneyL BarneyL · Accepted Answer · 2019-11-08T12:24:49

While you're not using actual dates you have values in ascending order so should be able to use similar patterns to standard year to date measures in DAX and Power BI. Credit here should go to the SQLBI team behind the DAX Patterns website I'm just customising their work here.

To keep things clear I would suggest breaking things down into steps, build year to date measures for both total lead time and count of measurements and then a third to give the actual average you need. Separate measures also help with debugging if there's an issue.

I'm assuming your data table is named 'MyTable'.

[TotalLeadTimeYTD] := 
CALCULATE (
    SUM('MyTable'[Leadtime]), 
    FILTER (
        ALL ( 'MyTable' ), 
        'MyTable'[FY] = MAX ( 'MyTable'[FY] )
            && 'MyTable'[Period] <= MAX ( 'MyTable'[Period] )
    )
)

And:

[CountOfMeasuresYTD] := 
CALCULATE (
    COUNTROWS('MyTable'), 
    FILTER (
        ALL ( 'MyTable' ), 
        'MyTable'[FY] = MAX ( 'MyTable'[FY] )
            && 'MyTable'[Period] <= MAX ( 'MyTable'[Period] )
    )
)

And Finally

AverageLeadTimeYTS := DIVIDE([TotalLeadTimeYTD],[CountOfMeasuresYTD])

Be aware that because you don't have a separate date table the ALL ( 'MyTable' ) of the calculations will also strip out any other filters you have applied. If this isn't the desired result you will need to change so that filters on period and year are removed but not others. ALL('MyTable'[FY],'MyTable'[Period] ) may work but we're at the point where it's hard to code without sample data.

The above measures have been tested against the following hard coded data set:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcos0tFDSUQowBBKGSrE6CBEjIGGEImIMJIxhIuYYusyRdMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "FY"}, {"Column2", "Period"}, {"Column3", "Leadtime"}})
in
    #"Renamed Columns"

Power BI YTD calculation in montly bases

1 Answers