Time Series Analysis: YTD TLL for previous yrs overstated vs YTD TTL for incomplete current yr

[DAXRichArd]

Matrix Table Output:

• My most current data ends Nov 2022.
  • 11 months of data.
• Previous years all have 12 months of data.
  • In the dashboard, I use a slicer and multi select the most current 11 months (Jan - Nov 2022).
    • The totals for all columns are correct.

Example output demonstrated in excel with pic of slicers being used:

In my measures I use a logic condition to check if any months in the most current year are missing.

• If there are missing months, do not display that month.
  • Since there is no data for December 2022, all December results are not displayed.
    • Jan - Nov are displayed.
    • This is working as well.

Problem

• In the dashboard, when all months are selected in the slicers, year to date columns do not display December.
  • That is ok. That is what I want.
• However, December is added to their totals.
  • This is not ok.

Pic below is for demonstration purposes.

• In the dashboard, Dec is not displayed.

Desired Outcome

Select ALL in the dashboard slicer,

• display only the months for the most current time series (Jan-Nov 2022) and
• have totals be correct for the months displayed (Jan-Nov 2022).

Actual dashboard table matrix pic below with text above totals that are not correct:

PBI File

https://myaccount.dropsend.com/file/2f593847fa068f03

If link expires, msg me to add new download link.

Your time is greatly valued.

Thx!

[jgeddes · Accepted solution]

You can try the following. Based on the little bit of testing I did they seem to work.

Create a measure that determines the maximum month number from the report dates...

maxMonthNumber = 
MONTH(
    CALCULATE(
        MAX('F-Cargo'[report_date]),
        ALLEXCEPT(_Calendar, _Calendar[Year])
    )
)

Then change the following measures...

Cargo MT LM = 
SUMX(
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]),
    CALCULATE(
        IF(
            ISBLANK([Cargo MT]),
            BLANK(),
            CALCULATE([Cargo MT], PREVIOUSMONTH(_Calendar[Date]))
        )
    )
)

Cargo MT LY = 
SUMX(
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]),
    CALCULATE(
        IF(
            ISBLANK( [Cargo MT] ),
            BLANK(),
            CALCULATE([Cargo MT], SAMEPERIODLASTYEAR((_Calendar[Date])))
        )
    )
)

Cargo MT YTD -2 Yr = 
var _vTable =
SUMMARIZE(
    _Calendar,
    _Calendar[Month],
    "__MT2Yr", IF(
                    ISBLANK([Cargo MT]),
                    BLANK(), 
                    TOTALYTD([Cargo MT], DATEADD('_Calendar'[Date], -2, YEAR))
                )
)
Return
MAXX(_vTable,[__MT2Yr])

Cargo MT 2019 = 
var _vTable =
SUMMARIZE(
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]),
    _Calendar[Year],
    _Calendar[Month],
    "__MT2019", IF(
                    ISBLANK([Cargo MT]),
                    BLANK(), 
                    CALCULATE([Cargo MT], DATESINPERIOD(_Calendar[Date], DATE(2019,MONTH(MIN(_Calendar[Date])),01), 1, MONTH))
                )
)
Return
SUMX(_vTable,[__MT2019])

Cargo MT YTD LY = 
var _vTable =
SUMMARIZE(
    _Calendar,
    _Calendar[Month],
    "__MTLY", IF(
                    ISBLANK([Cargo MT]),
                    BLANK(), 
                    TOTALYTD([Cargo MT], DATEADD('_Calendar'[Date], -1, YEAR))
                )
)
Return
MAXX(_vTable,[__MTLY])

Cargo MT YTD 2019 = 
var _vTable =
SUMMARIZE(
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]),
    _Calendar[Year],
    _Calendar[Month],
    "__MT2019YTD", IF(
                    ISBLANK([Cargo MT]),
                    BLANK(), 
                    TOTALYTD([Cargo MT], DATESINPERIOD(_Calendar[Date], DATE(2019,MONTH(MIN(_Calendar[Date])),01), 1, MONTH))
                )
)
Return
MAXX(_vTable,[__MT2019YTD])

Hope this gets you pointed in the right direction.

[DAXRichArd]

Wow jgeddes,

Your amazing.

I hacked and chopped away at this for weeks.

I was moving in the right direction conceptually but I don't have the depth of knowledge that you have.

I will reverse engineer your work in the hopes of gaining some knowledge.

I am forever greatful to you for your help.

I also want to thank Ashish_Mathur on my initial problem. He was helpful and was leading me in the right direction.

jgeddes, your a godsend.

Forever greatful,

DAXRichard

[jgeddes]

Thanks for the kind words.

Here are the basic ideas around how this works

• First item was to create a measure that calculates the maximum month number from the report dates based on the year context that is applied in any given visual.

maxMonthNumber =
MONTH(//gets month number
    CALCULATE(
        MAX('F-Cargo'[report_date]), //calculates the maximum date
        ALLEXCEPT(_Calendar, _Calendar[Year]) //looks for all dates in the _Calendar table allowing the [Year] to be filtered or have a contextual filter applied e.g row in a matrix
    )
)

• Cargo MT LM Measure
  • Creates a virtual table of month numbers that are less than or equal to the maximum month number. The ability of the maxMonthNumber to have context filters applied is what changes the months in the virtual table based upon the year that is chosen. The sumx function then sums the previous month Cargo MT value for each month number in the virtual table.

Cargo MT LM =
SUMX(//iterates over the virtual table of month numbers, summing the Cargo MT value from the previous month
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]), //creates a virtual table of month numbers that are less than or equal to the maxMonthNumber
    CALCULATE(//returns the Cargo MT value from the previous month as long as it is not blank
        IF(
            ISBLANK([Cargo MT]),
            BLANK(),
            CALCULATE([Cargo MT], PREVIOUSMONTH(_Calendar[Date]))
        )
    )
)

• Cargo MT YTD -2Yr
  • Creates a virtual table in a variable of month names. (I often use variables with my virtual tables to make it easier to debug as you can take the virtual table code and use it to create a calculated table to visualize the table to make sure it is doing what you expect.) The virtual table has a column added that displays the Cargo MT YTD value for 2 years ago. The MAXX function iterates over the virtual table rows to return the max value, which in this case will be the last month.  

Cargo MT YTD -2 Yr =
var _vTable = //variable used to create the virtual table
SUMMARIZE(//summarize the _Calendar table by month names
    _Calendar,
    _Calendar[Month],
    "__MT2Yr", IF( //creates a column in the summarized table that displays the Cargo MT YTD value from 2 years ago as long as it is not blank
                    ISBLANK([Cargo MT]),
                    BLANK(),
                    TOTALYTD([Cargo MT], DATEADD('_Calendar'[Date], -2, YEAR))
                )
)
Return
MAXX(_vTable,[__MT2Yr]) //return the largest value in the YTD virtual table i.e. the last value

• Cargo MT 2019
  • Creates a virtual table that summarizes the _Calendar table by Year and Month Name for month numbers that are less than or equal to the maximum month number. A column is added to the virtual table that displays the Cargo MT value for each month name in the virtual table that is from the year 2019. (As long as the Cargo MT value is not blank for a given month name.) The SUMX function then sums the values in the virtual table.

Cargo MT 2019 =
var _vTable =
SUMMARIZE(//summarize the _Calendar table by Year and Month name for month numbers less or equal to the maxMonthNumber
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]),
    _Calendar[Year],
    _Calendar[Month],
    "__MT2019", IF(
                    ISBLANK([Cargo MT]),//if the Cargo MT value is blank, blank is returned else the monthly value from 2019 is returned
                    BLANK(),
                    CALCULATE([Cargo MT], DATESINPERIOD(_Calendar[Date], DATE(2019,MONTH(MIN(_Calendar[Date])),01), 1, MONTH)) // calculates the Cargo MT for each month in 2019 for each row in the virtual table
                )
)
Return
SUMX(_vTable,[__MT2019])// sums the values in the virtual table

• Cargo MT YTD LY
  • Creates a virtual table that summarizes the _Calendar table by month name, adding a column that returns the YTD value of Cargo MT for the year 1 less than the current context. The MAXX function returns the max value of the virtual table, in this case the last month’s value.

Cargo MT YTD LY =
var _vTable =
SUMMARIZE(//create a virtual table that summarizes the _Calendar table by month name
    _Calendar,
    _Calendar[Month],
    "__MTLY", IF(//add a column in the virtual table that displays the Cargo MT value for the year that is one less than the current year context
                    ISBLANK([Cargo MT]),
                    BLANK(),
                    TOTALYTD([Cargo MT], DATEADD('_Calendar'[Date], -1, YEAR))
                )
)
Return
MAXX(_vTable,[__MTLY])//returns the largest value in the virtual table (last month's value)

• Cargo MT YTD 2019
  • Create a virtual table that summarizes _Calendar table by Year and Month Name for all months that are less than or equal to maxMonthNumber. A column is added that displays the YTD value for each month in the year 2019. The MAXX function returns the largest value of the virtual table, which is the latest month’s value.

Cargo MT YTD 2019 =
var _vTable =
SUMMARIZE(//create a virtual table that summarizes the _Calendar table by Year and Month Name for any month numbers that are less than or equal to the maxMonthNumber
    FILTER(_Calendar, _Calendar[MonthNu] <= [maxMonthNumber]),
    _Calendar[Year],
    _Calendar[Month],
    "__MT2019YTD", IF(//adds a column that displays the Cargo MT value for months where Cargo MT is not blank
                   ISBLANK([Cargo MT]),
                    BLANK(),
                    TOTALYTD([Cargo MT], DATESINPERIOD(_Calendar[Date], DATE(2019,MONTH(MIN(_Calendar[Date])),01), 1, MONTH))//calculates YTD value of Cargo MT for each month in 2019
                )
)
Return
MAXX(_vTable,[__MT2019YTD])//returns the largest value in the virtual table, ie the last month's value

 Cheers