cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkCBB Member
Member

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

Accepted Solutions
Super User
Super User

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

@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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




17 REPLIES 17
hemantsingh Member
Member

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

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

MarkCBB Member
Member

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

Hello @hemantsingh

 

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

MarkCBB Member
Member

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

Here is a sample data set with the following tables:

 

FACT, PLACES, CALENDER 

DATA

Highlighted
hemantsingh Member
Member

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

@MarkCBB,

 

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

 

Regards

hemantsingh Member
Member

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

 

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

MarkCBB Member
Member

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

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.

 

 

 

MarkCBB Member
Member

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

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)

MarkCBB Member
Member

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

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. 

MarkCBB Member
Member

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

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