Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkCBB
Helper V
Helper V

DAX YTD Different end dates per channel - data dates consistency problem

Hi there,

 

I have been given an interesting task to update our current YTD totals. 

 

TYTD = This Year to Date

PYTD = Previous Year to Date

 

here is the YTD Measure:

TYTD UNITS = TOTALYTD([TOTAL UNITS],'CALENDAR'[DATE])

The TOTAL UNITS Measure is as follows:

TOTAL UNITS = SUM('FACT'[UNITS])

These are my tables, (Star configuration)

  • FACT
  • PRODUCTS (The product link table is just to link up the products as the same product has 2-3 ID's)
  • PLACES (KNOWN as MASTER_SITE)
  • CALENDAR

 

TABLE LAYOUT

 

Inside the MASTER_SITE (PLACES) table, I have a column called CHANNEL Different retail groups).  - There are 4 channels.

For each channel, the data is updated on different periods, i.e. GROUP 1 gets updated daily, GROUPS 2-3 Get updated Monthly at the end of the Month, and GROUP 4 gets updated monthly the following month, thus when I do the TYTD calculation I get the latest YTD Units.

 

However when I want to work out the growth from this year vs Previous year - I use the following calculation:

TYTD UNITS GROWTH = DIVIDE([TYTD UNITS]-[PYTD UNITS],[PYTD UNITS])

The PYTD Measure is as follows:

PYTD UNITS = TOTALYTD([TOTAL UNITS],SAMEPERIODLASTYEAR('CALENDAR'[DATE]))

This is where the problem I am facing is, as the different Channels/Groups have different date periods it is not giving me a fair TYTD UNITS GROWTH Results as the PYTD UNITS is taking all the months data from each of the channels (i.e. 2016-01 to 2017-07) and the TYTD UNITS is taking the following:

 

  • GROUP 1 2016-01 to 2017-07 - this is fine as the previous year data is consistent
  • GROUP 2-3 is measuring (PYTD = 2016-01 to 2017-07) against (TYTD = 2017-01 to 2017-06) - this is not fine as there is a month missing, thus the previous year should only go up until 2017-06 as there is not data for these groups in 2017-07 yet.
  • GROUP 4 is measuring (PYTD = 2016-01 to 2017-07) against (TYTD = 2017-01 to 2017-05) which is the same problem as the above 2 groups but worse off by another month.

so I am looking for a way to update the TYTD UNITS GROWTH and the PYTD UNITS measures in order for them to take into account the latest date contest for/within the different Channels.

 

Looking forward to hearing back from anyone.

 

Regards,

Mark B

 

 

 

 

1 ACCEPTED SOLUTION

@MarkCBB

 

Here is an example of how I would do it.

I've used your Excel data posted earlier.

 

The logic is to iterate over the values of Channel using SUMX, and for each Channel determine the last date for which data exists, and if that date is earlier than the last selected date, cut off the PYTD calculation at that date.

 

Uploaded sample pbix here.

 

The PYTD UNITS measure looks like this:
(Note: I would have written this slightly differently using TREATAS, but there seems to be a bug with TREATAS in Power BI Desktop at the moment)

 

PYTD UNITS = 
SUMX (
    VALUES ( CHANNEL[CHANNEL] ),
    // Iterate over  CHANNELs
    VAR CalendarLastDateSelected = 
        MAX ( 'CALENDAR'[DATE] )
        // Last date selected on CALENDAR table
    VAR ChannelLastDateGlobal =
        CALCULATE ( MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', 'CHANNEL' ) )
        // Last date that exists in FACT for the current CHANNEL (ignoring any filters other than CHANNEL)
    VAR ChannelLastDateApplied =
        MIN ( CalendarLastDateSelected, ChannelLastDateGlobal )
        // The earlier of these is the date to apply for PYTD calculation  
    RETURN 
        CALCULATE ( 
            [TYTD UNITS],
            SAMEPERIODLASTYEAR( 'CALENDAR'[DATE] = ChannelLastDateApplied )
        )
)

 

You might also want to edit the measures so that YTD or PYTD values are hidden per channel for dates later than the maximum existing date, using the method on this page:

http://www.daxpatterns.com/cumulative-total/

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

17 REPLIES 17
hemantsingh
Helper V
Helper V

Hi @MarkCBB,

 

   Can you please share some sample data that has various channel & update dates dates so that i can replicate the same at my end & help you in a more intutive way??

 

Regards

Hello @hemantsingh

 

The data is in SQL, let me quickly create some sample data that has the same problem.

Here is a sample data set with the following tables:

 

FACT, PLACES, CALENDER 

DATA

@MarkCBB,

 

  got the sample..lemme explore it & try to help you.

 

Regards

 

Data_PBI_YTD.JPG

 

hi @MarkCBB,

 

    this is the measure i used to calcuate YTD.

 

total ytd = if(
min('Date'[Date])<=calculate(max('FACT'[DATE]),all('FACT')),
CALCULATE([sum of units],filter(all('Date'[Date]),'Date'[Date]<=max('Date'[Date])),values('FACT'[channel]))
)

 

Note:- I have created a Date table as the calendar table of urs do not have continuos dates.

 

Lemme know if the formula helps you in resolving your situation.

 

Regards,

Hemant

Thank you, but that doesn't account for the problem that I am facing.

 

the one channel has current year date for 2017-01-01 to 2017-07-19) whereas the other channels do not have data for the latest 1 or 2 months, thus they are being measured on Months that do not have data yet. 

 

so if the channel only has data for 2017-01-01 to 2017-05-30 (5 months instead of 7) it should be compared to 2016-01-01 to 2016-07-19) it Should be compared to 2016-01-01 to 2016-05-30) I think that I need to have a PTYD measure for each channel and then SUM them together to the Actual PYTD total. Each one for the different channels will need to go up until the latest month/Date of data for that channel for the same time the previous year.

 

 

 

This seems to work if I filter down to one channel:

PYTD2 UNIT = TOTALYTD([TOTAL UNITS],SAMEPERIODLASTYEAR(DATESYTD('FACT'[DATE])))

this takes into account that the latest sales month is not the latest month - However, when I include all the channels, I get the same incorrect result (Well it is correct according to the DAX but not correct in accordance to what I am looking for)

This one works for one channel and also takes into account the context, i.e. if I filter to another channel this goes blank:

PYTD2 UNIT = CALCULATE(TOTALYTD([TOTAL UNITS],SAMEPERIODLASTYEAR(DATESYTD('FACT'[DATE]))),FILTER(MASTER_SITE,MASTER_SITE[CHANNEL]="SPAR"))

-Note the filter at the end of the above measure

 

I think if I had one of these for each channel and then sum the results of each together I will get the result I am looking for, but I need it to be more dynamic as I don't want to create new measures each time a channel is added. 

Correction to the above post, when I created one for each channel, all measures go blank when I filter on a channel 😞

@MarkCBB

 

Here is an example of how I would do it.

I've used your Excel data posted earlier.

 

The logic is to iterate over the values of Channel using SUMX, and for each Channel determine the last date for which data exists, and if that date is earlier than the last selected date, cut off the PYTD calculation at that date.

 

Uploaded sample pbix here.

 

The PYTD UNITS measure looks like this:
(Note: I would have written this slightly differently using TREATAS, but there seems to be a bug with TREATAS in Power BI Desktop at the moment)

 

PYTD UNITS = 
SUMX (
    VALUES ( CHANNEL[CHANNEL] ),
    // Iterate over  CHANNELs
    VAR CalendarLastDateSelected = 
        MAX ( 'CALENDAR'[DATE] )
        // Last date selected on CALENDAR table
    VAR ChannelLastDateGlobal =
        CALCULATE ( MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', 'CHANNEL' ) )
        // Last date that exists in FACT for the current CHANNEL (ignoring any filters other than CHANNEL)
    VAR ChannelLastDateApplied =
        MIN ( CalendarLastDateSelected, ChannelLastDateGlobal )
        // The earlier of these is the date to apply for PYTD calculation  
    RETURN 
        CALCULATE ( 
            [TYTD UNITS],
            SAMEPERIODLASTYEAR( 'CALENDAR'[DATE] = ChannelLastDateApplied )
        )
)

 

You might also want to edit the measures so that YTD or PYTD values are hidden per channel for dates later than the maximum existing date, using the method on this page:

http://www.daxpatterns.com/cumulative-total/

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger

 

I noticed in your gobal date variable, you use just the tables as arguments for ALLEXCEPT (   ALLEXPECT (<table>, <column> [, <column>[,…]])

 

ALLEXCEPT ( 'FACT', 'CHANNEL' )

I did not even know it was possible. That's pretty cool!  Any specific reason behind that?

I am only asking because using CHANNEL tbl and column probably it will also work.
ALLEXCEPT (CHANNEL, CHANNEL[CHANNEL]

@nickchobotar I got an error on that piece I had to update it to this:

ALLEXCEPT (CHANNEL, CHANNEL[CHANNEL] ) 

@MarkCBB

Yes, your assumption is correct. Within the PYTD DAX formula, the variable ChannelLastDateGlobal is the last date which appears in FACT for a given Channel, so may not be a month-end date.

 

You might want to use more sophisticated logic to handle cases where a particular month has passed but the last transaction date in that month was not the last day of the month. For example, for Channel TOPS, if the last transaction date were 29 May, PYTD would be cut-off at 29 May, whereas 31 May would make sense given we know May has completely passed. One way of doing this might be to 'round up' ChannelLastDateGlobal to the end of the month if any Channel has dates from later months.

 

@nickchobotar

Yes, ALLEXCEPT can be used with an entire related table as the 2nd argument, instead of a column of a the expanded table from the 1st argument.

 

In this case, ALLEXCEPT ( 'FACT', 'CHANNEL' ) clears all filters on any columns of 'CHANNEL' from the expanded 'FACT' table (i.e. 'FACT' left-outer-join all related tables in this model). You could use ALLEXCEPT ( 'FACT', CHANNEL[CHANNEL] ) in this case since we were only filtering on the CHANNEL column anyway. The intention here is to get the global max date for a Channel by clearing all filters on the 'FACT' table except Channel.

 

I don't think ALLEXCEPT ( CHANNEL, CHANNEL[CHANNEL] ) would have the desired effect, since we want to ensure we get a global max date for a given Channel, so we want to clear any non-Channel filters that would filter 'FACT' (such as 'CALENDAR' ). Clearing filters on CHANNEL is not sufficient.

 

Cheers,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you for the feedback @OwenAuger - That is what I was hoping it would do.

You are correct, I had to change the ALLEXCEPT ( CHANNEL, CHANNEL[CHANNEL] )  back to the way you had it.

 

With regards to the advice you gave 

"You might want to use more sophisticated logic to handle cases where a particular month has passed but the last transaction date in that month was not the last day of the month. For example, for Channel TOPS, if the last transaction date were 29 May, PYTD would be cut-off at 29 May, whereas 31 May would make sense given we know May has completely passed. One way of doing this might be to 'round up' ChannelLastDateGlobal to the end of the month if any Channel has dates from later months."

 

Thankfully this will not happen within this dataset as there is more than likely more sales that occur on the last date of the month. but out of interest how would you do this? I am a newbie so enjoying and appreciating the teachings you are providing. 

 

Based on the Function you provide I have created the following which after some manual checks all seems to be working.

  • TYTM - This Year This Month
  • TYPM - This Year Previous Month
  • PYTM - Previous Year This Month
  • TYTQ - This Year This Quater
  • TYPQ - This Year Previous Quater
  • PYTQ - Previous Year this Quater

Below are the functions:

TYTM UNITS = 
SUMX (
	VALUES ( MASTER_SITE[CHANNEL] ),
	VAR CalendarLastDateSelected = 
		MAX ( 'CALENDAR'[DATE] )
	VAR ChannelLastDateGlobal =
		CALCULATE (	MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', MASTER_SITE[CHANNEL] ) )
	VAR ChannelLastDateApplied =
		MIN ( CalendarLastDateSelected, ChannelLastDateGlobal )
	RETURN 
		CALCULATE ( 
			CALCULATE([TOTAL UNITS],DATESMTD('CALENDAR'[DATE])),
			DATESMTD('CALENDAR'[DATE] = ChannelLastDateApplied)
		)
)
TYPM UNITS = 
SUMX (
	VALUES ( MASTER_SITE[CHANNEL] ),
	VAR CalendarLastDateSelected = 
		MAX ( 'CALENDAR'[DATE] )
	VAR ChannelLastDateGlobal =
		CALCULATE (	MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', MASTER_SITE[CHANNEL] ,'CALENDAR'[Month Year]) )
	VAR ChannelLastDateApplied =
		MIN ( CalendarLastDateSelected, ChannelLastDateGlobal ) 
	RETURN 
		CALCULATE ( 
			CALCULATE([TOTAL UNITS],DATESMTD('CALENDAR'[DATE])) ,
			PREVIOUSMONTH('CALENDAR'[DATE].[Date] = ChannelLastDateApplied)
		)
)
PYTM UNITS = 
SUMX (
	VALUES ( MASTER_SITE[CHANNEL] ),
	VAR CalendarLastDateSelected = 
		MAX ( 'CALENDAR'[DATE] )
	VAR ChannelLastDateGlobal =
		CALCULATE (	MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', MASTER_SITE[CHANNEL] ) )
	VAR ChannelLastDateApplied =
		MIN ( CalendarLastDateSelected, ChannelLastDateGlobal ) 
	RETURN 
		CALCULATE ( 
			CALCULATE([TOTAL UNITS],DATESMTD(DATEADD('CALENDAR'[DATE],-1,YEAR))),
			DATESMTD('CALENDAR'[DATE] = ChannelLastDateApplied)
		)
)
TYTQ UNITS = 
SUMX (
	VALUES ( MASTER_SITE[CHANNEL] ),
	VAR CalendarLastDateSelected = 
		MAX ( 'CALENDAR'[DATE] )
	VAR ChannelLastDateGlobal =
		CALCULATE (	MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', MASTER_SITE[CHANNEL] ) )
	VAR ChannelLastDateApplied =
		MIN ( CalendarLastDateSelected, ChannelLastDateGlobal ) 
	RETURN 
		CALCULATE ( 
			CALCULATE([TOTAL UNITS],DATESQTD('CALENDAR'[DATE])),
			DATESQTD('CALENDAR'[DATE] = ChannelLastDateApplied)
		)
)
TYPQ UNITS = 
SUMX (
	VALUES ( MASTER_SITE[CHANNEL] ),
	VAR CalendarLastDateSelected = 
		MAX ( 'CALENDAR'[DATE] )
	VAR ChannelLastDateGlobal =
		CALCULATE (	MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', MASTER_SITE[CHANNEL]))
	VAR ChannelLastDateApplied =
		MIN ( CalendarLastDateSelected, ChannelLastDateGlobal )
	RETURN 
		CALCULATE ( 
			CALCULATE([TOTAL UNITS],DATESQTD('CALENDAR'[DATE])) ,
			PREVIOUSQUARTER('CALENDAR'[DATE].[Date] = ChannelLastDateApplied)
		)
)
PYTQ UNITS = 
SUMX (
	VALUES ( MASTER_SITE[CHANNEL] ),
	VAR CalendarLastDateSelected = 
		MAX ( 'CALENDAR'[DATE] )
	VAR ChannelLastDateGlobal =
		CALCULATE (	MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', MASTER_SITE[CHANNEL] ) )
	VAR ChannelLastDateApplied =
		MIN ( CalendarLastDateSelected, ChannelLastDateGlobal )
	RETURN 
		CALCULATE ( 
			CALCULATE([TOTAL UNITS],DATESQTD(DATEADD('CALENDAR'[DATE],-1,YEAR))),
			DATESQTD('CALENDAR'[DATE] = ChannelLastDateApplied)
		)
)

 

Out of interest, I have noticed that If I add a Column Chart Visual with Year-Month as the axis and Total Units as the value, and I select a previous Month, some of the above measures show as (Blank), I assume that this is due to the context of the dates changing the measure? If I wanted to update the function so that if I clicked on one of the Previous months, the function then thinks that that month is the latest month in the data so the user could in effect roll back the report up until that month. So If the latest month of all the data was July and the user clicked on the June Month column in that chart, the functions would update as if there was no July data yet. Could the above functions be edited in such a way to achieve this?

 

Looking forward to your response. 

 

Regards,

Mark B

 

@MarkCBB

Apologies for late reply.

 

On your first question about handling cases where the last transaction wasn't on an end-of-month date, I was thinking of something like this:

  1. Find the last date in the FACT table across all Channels (with all filters removed), and call this date OverallLastDateGlobal
  2. Increase ChannelLastDateGlobal up to OverallLastDateGlobal, but don't go beyond the end of the month. Call this date ChannelLastDateGlobalModified.

This would mean, for example:

  • If  Channel A had transactions up to 28 May, but Channel B had transactions up to 31 July, Channel A's last date would be treated as 31 May. Channel B's transactions indicate that May must be complete, and Channel A just happened to have no transactions from 29 May to 31 May.
  • If Channel A had transactions up to 14 May, but Channel B had transactions up to 28 May, Channel A's last date would be treated as 28 May. Channel B's transactions indicate that time has passed up to 28 May, and Channel A just happened to have no transactions after 14 May.

The code would look like:

 

 

PYTD UNITS = 
SUMX (
    VALUES ( CHANNEL[CHANNEL] ),
    // Iterate over  CHANNELs
    VAR CalendarLastDateSelected = 
        MAX ( 'CALENDAR'[DATE] )
        // Last date selected on CALENDAR table
    VAR ChannelLastDateGlobal =
        CALCULATE ( MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', 'CHANNEL' ) )
        // Last date that exists in FACT for the current CHANNEL (ignoring any filters other than CHANNEL)
VAR OverallLastDateGlobal =
CALCULATE ( MAX ( 'FACT'[DATE] ), ALL ( 'FACT' ) )
VAR ChannelLastDateGlobalModified =
MAX ( ChannelLastDateGlobal, MIN ( OverallLastDateGlobal, EOMONTH ( ChannelLastDateGlobal, 0 ) ) ) VAR ChannelLastDateApplied = MIN ( CalendarLastDateSelected, ChannelLastDateGlobalModified ) // The earlier of these is the date to apply for PYTD calculation RETURN CALCULATE ( [TYTD UNITS], SAMEPERIODLASTYEAR( 'CALENDAR'[DATE] = ChannelLastDateApplied ) ) )

 

I will have to get back to you on your "previous month" query, unless someone else tackles it in the mean time 🙂

 

Cheers,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

CLIP

 

above is a clip of how it is interacting (Note the values will be different as the data is the full dataset)

 

in the clip, you will see what happens when One month is selected, and near the end of the clip, you will see how I would like it to work (When I select all months up until the 2nd last month.

 

so Instead of filtering to the month, it should filter up-until the month. Hope that makes sense?

Thank you @OwenAuger

 

This looks great - very clever. (I am really keen to see how you would have used TREATAS when the bug is fixed, I have never heard of that function before. I read the pop-up and seems like a very helpful function to know)

 

I am doing some manual checks and I found something that does not make sense to me, but this could just be my understanding:

 

If I manually sum the totals for each channel for the same consistency months for the previous year as the current year I get a different value.

Below I get 958,523, However, if I drop the PYTD Measure into a card visual I get 944,797).

 

Could you explain this to me? (My only assumption is that the table below for the items in yellow are the SUM for all the dates within the Month whereas the current year's Month might not end on the end date, of the month thus pushing back the PYTD to that specific date within the corresponding month - If this assumption is correct then this is exactly what I am looking for, if not could you explain to me how it is working?

 

Capture

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.