power bi modeling / DAX challenge for non-conventional YOY comparison

[jj_0511]

Hello:

There are 3 data sets: Date, Store and Sales.

Date:

Values in Date column is unique, values in “related date in 2019” is not unique.

Sample data:

Date	related date in 2019
1-1-2023	1-6-2019
1-2-2023	1-7-2019
1-3-2023	1-8-2019
1-4-2023	1-9-2019
1-5-2023	1-10-2019
1-6-2023	1-11-2019

Store:

store	location
A	X
B	X
C	Y

Sales:

Sample data:

date	store	sales
2/1/2023	A	8
2/9/2023	A	9
2/14/2019	A	7
2/14/2019	A	7
2/1/2023	B	4
2/9/2023	B	6
2/14/2019	B	4
2/14/2019	B	3
2/1/2023	C	8
2/9/2023	C	7
2/14/2019	C	4
2/14/2019	C	3

Required report:

Date	Daily Sales	WTD sales	MTD sales	YTD sales	Daily Sales 2019	WTD sales 2019	MTD sales 2019	YTD sales 2019
2/1/2023	20	20	20	20	25	25	25	25
2/6/2023	18	18	38	38	35	35	60	60
2/9/2023	22	40	60	60	28	63	88	88
2/2/2022	…	…	…	…	…	…	…	…
2/7/2022	…	…	…	…	…	…	…	…
2/10/2022	…	…	…	…	…	…	…	…

Daily Sales 2019, WTD (week to date) sales 2019, MTD (month to date) sales 2019, YTD (year to date) sales 2019 are sales for related dates in 2019 based on what’s in Date table.

For example, MTD sales 2019 for 2/9/2023 is calculated as:

For 2/9/2023, MTD dates with sales are 2/1/2023, 2/6/2023 and 2/9/2023. The corresponding dates in 2019 for these dates are 2/6/2019, 2/11/2019 and 2/14/2019. As a result, MTD sales 2019 for 2/9/2023 is calculated as total sales for 2/6/2019, 2/11/2019 and 2/14/2019.

Daily/WTD/YTD Sales 2019 follows same logic.

The output should be sliceable by dimensions linked with sales table such as store/date.

How to properly model/DAX for the required output?

pbix: pbix file 

[TomMartens]

Hey @jj_0511 ,

in this pbix you will find some measures that allow you to create the below report:

The measures use default time intelligence functions except the Sales WTD and Sales WTD 2019, these two measures are a little more complex because a week is a more complex thing.
I also added a new relationship to the data model:

This 2nd relationship between the date and the sales table will be activated in the 2019 measures and allows to filter the sales table based on the 2019 reference dates in the date table accordingly.
The following shows the Sales WTD 2019 measure as it is the most complex one:

Sales WTD 2019 = 
IF( NOT( ISBLANK( [Sales Daily] ) )
    , var currentdate = SELECTEDVALUE( 'Date'[Date] )
    var StartofWeek = SELECTEDVALUE( 'Date'[SoWDate] )
    return
    CALCULATE(
        [Sales Daily]
        , DATESBETWEEN( 'Date'[Date] , StartofWeek , currentdate )
        , USERELATIONSHIP( 'Date'[related date in 2019] , Sales[date] )
        , CROSSFILTER( 'Date'[Date] , Sales[date] , None )
    )
    , BLANK()
)

The activation of the relationship is happening by using the function USERELATIONSHIP. To avoid any interference with the active/default relationship I use CROSSFILTER( ... , ... , None).

This measure makes also use of the additional column "SoWDate", I added to the date table. The week to date is happening by setting the filter spanning the appropriate dates using the funtion DATESBETWEEN.

You must check if this works for the WTD 2019 measure, if not you must provide a SoWDate2019 column in your date table.

I also omitted the creation of the YTD measures as these measures are similar to the MTD measurs.

Hopefully, this provides what you are looking for and helps to tackle your challenge.

Regards,
Tom

[jj_0511]

Thanks @TomMartens This is very helpful. But there's something in your code I have trouble understanding:

...
CALCULATE(
        [Sales Daily]
        , DATESBETWEEN( 'Date'[Date] , StartofWeek , currentdate )
        , USERELATIONSHIP( 'Date'[related date in 2019] , Sales[date] )
        , CROSSFILTER( 'Date'[Date] , Sales[date] , None )
    )
...

Why is crossfilter() needed here? You said "To avoid any interference with the active/default relationship I use CROSSFILTER( ... , ... , None)".  If I remove crossfilter(),  Sales WTD 2019 for Feb-9-2023 is 40 instead of correct value of 63. How is the value of 40 calculated when crossfilter() is not used? Could you help me undertand this? Thanks.

[TomMartens]

Hey @jj_0511 ,

please provide a data table that contains the week definitions and describe if the week is an ISO week. To solve this challenge I would use a calculation group for all the values like daily, salesWTD , ...
I do not understand the value of salesDaily and sales 2019, please elaborate on the expected results for these calculation group items.
Your explanation to calculate the MTD 2019 for the date 2023-02-09 contains dates like 2019-02-11, but there is no sample data in the sales table, elaborate on this as well.

Next, make sure that no login/access confirmation is required to get your sample pbix.

Regards,

Tom

[jj_0511]

@TomMartens 

1. week is defined as Monday to Sunday;

2. Date table contains dates and their corresponding dates in 2019. 

Sales daily for 2/1/2023 is total sales for 2/1/2023;

Sales daily 2019 for 2/1/2023 is sales for the 2019 date corresponding to 2/1/2023, which is 2/6/2019 based on Date table;

Similarly, suppose we need to calculate MTD 2019 values for 2/9/2023:  MTD dates are 2/1/2023 - 2/9/2023, then we find corresponding 2019 dates for 2/1/2023 to 2/9/2023 in Date table which is 2/6/2019 to 2/14/2019. Therefore, MTD 2019 values for 2/9/2023 is sales for 2/6/2019 to 2/14/2019. 

In short, we need to find corresponding 2019 dates in Date[related date in 2019] first to calculate values for 2019.

3. There may not be sales for cerain dates in Sales table. We just need aggregated sales for current period vs its corrresponding 2019 dates.

4. pbix file is now open to public

5. a related post is : data modeling / reporting problem This post is an expansion on that one.

Thanks for your help.

[TomMartens]

Hey @jj_0511 ,

share the xlsx files that are used as a data source as well, otherwise, it will not be possible to adapt the data model using Power Query.

Regards,

Tom

[jj_0511]

@TomMartens Please find sample data here data Thanks!