Table of equal weeks of current and previous year

[esarel]

Hi experts,

For one of the graphs that I have to make, I compare a total number of a month of a year with the same month of the previous year. For example, December 2022 and December 2021. But to compare it instead of comparing the whole month, I would only like to compare the same days of the week, that is, the first Monday of December 2022 with the corresponding Monday of December 2021.

An example of this:

    

The comparison of December 2022 and 2021 would start with Monday, November 28, 2022 being compared to Monday, November 29, 2021.

To compare this I have thought to make a table where we have:

date - same calendar day previous year - same business day previous month

Resulting in something like this:

The latter is wanted for the full year, from 2019 to 2023, in each of its months.

Thanks for the help!!

[PaulDBrown]

Ok, we need to add two more columns to the Date Table:

DayOfMonth = 
VAR _SelWeek = 'Date Table'[Week]
VAR _PrevYear = 'Date Table'[Year] -1
VAR _DayOfWeek = 'Date Table'[DayOfWeek]
VAR _Day = DAY('Date Table'[Date])
VAR _Calc = CALCULATE(MAX('Date Table'[Date]), 
                FILTER(ALL('Date Table'),
                'Date Table'[DayOfWeek] = _DayOfWeek 
                && 'Date Table'[Week] = _SelWeek 
                && 'Date Table'[Year] = _PrevYear))
VAR _RES =  IF(ISBLANK(_Calc), BLANK(), _Day & "(" & DAY(_Calc) & ")")
RETURN
CALCULATE(
    _RES, ALLEXCEPT('Date Table','Date Table'[Date]))

and to sort the DayOfMonth column

Sort DOM =
VAR _SelWeek = 'Date Table'[Week]
VAR _PrevYear = 'Date Table'[Year] - 1
VAR _DayOfWeek = 'Date Table'[DayOfWeek]
VAR _Day =
    DAY ( 'Date Table'[Date] )
VAR _Calc =
    CALCULATE (
        MAX ( 'Date Table'[Date] ),
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[DayOfWeek] = _DayOfWeek
                && 'Date Table'[Week] = _SelWeek
                && 'Date Table'[Year] = _PrevYear
        )
    )
VAR _RES =
    IF (
        ISBLANK ( _Calc ),
        BLANK (),
        VALUE ( DAY ( 'Date Table'[Date] ) & DAY ( _Calc ) )
    )
RETURN
    CALCULATE ( _RES, ALLEXCEPT ( 'Date Table', 'Date Table'[Date] ) )

Create the % measure (adapt to how you wish to see the variance)

% vs Prev Year = 
DIVIDE([Sum Sales], [Same Week Previous Year])

Create a new measure to use as a filter to show only 4 full weeks:

FILTER 4 weeks =
VAR _MinMonday =
    CALCULATE (
        MIN ( 'Date Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Date Table' ), 'Date Table'[DayOfWeek] = 1 )
    )
VAR _MaxSunday =
    CALCULATE (
        MAX ( 'Date Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Date Table' ), 'Date Table'[DayOfWeek] = 7 )
    )
RETURN
    IF (
        AND (
            MAX ( 'Date Table'[Date] ) >= _MinMonday,
            MAX ( 'Date Table'[Date] ) <= _MaxSunday
        ),
        1
    )

Now build the visual using fields from the Date Table and the measures. You can leave the Week or month fields out.

To filter the visual to see only 4 full weeks, add the 'Date Table'[Date] filed to the filter pane for the visual, select TopN for the filter type, add the [FILTER 4 weeks] measure and set the TopN value to 1:

I've attached the sample PBIX file

[esarel]

Thank you very much everything works perfectly with these measures!

But, one question, in case you want to put a date filter that instead of four weeks starts with Monday but not the first Monday of the month, for example in December 2021 the 1st is Wednesday but we want it to start from the 29th of November (Monday). And the end date is the last day of the selected month, in the case of December the 31st.
How would this be done with a measure?

[PaulDBrown]

Is it always the Monday before the first selected date (unless the selected date is a Monday)?

Will the last date be the last selected date even if it isn't a Sunday?

[esarel]

Yes

In addition, the selector is only for month and year, so we would always take into account the 1/month/year and the last day.

[PaulDBrown]

First of all, the Sort SOM Order column in the Date Table must be modified, since I realised it's not sorting properly. Use this code:

Sort DOM =
VAR _SelWeek = 'Date Table'[Week]
VAR _PrevYear = 'Date Table'[Year] - 1
VAR _DayOfWeek = 'Date Table'[DayOfWeek]
VAR _Day =
    DAY ( 'Date Table'[Date] )
VAR _Calc =
    CALCULATE (
        MAX ( 'Date Table'[Date] ),
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[DayOfWeek] = _DayOfWeek
                && 'Date Table'[Week] = _SelWeek
                && 'Date Table'[Year] = _PrevYear
        )
    )
VAR _RES =
    IF (
        ISBLANK ( _Calc ),
        BLANK (),
        DAY ( 'Date Table'[Date] ) * 100
            + DAY ( _Calc )
    )
RETURN
    CALCULATE ( _RES, ALLEXCEPT ( 'Date Table', 'Date Table'[Date] ) )

For the new request, here is one way.  Create a duplicate of the Date Table Using:

Date SLicer =
SELECTCOLUMNS (
    'Date Table',
    "Sel Date", 'Date Table'[Date],
    "Sel MonthNumber", 'Date Table'[MonthNum],
    "Sel Month", 'Date Table'[Month],
    "Sel Year", 'Date Table'[Year]
)

Leave this new table unrelated in the model:

Use the fields from this Date Slicer table for the slicers, and the fields from the Date Table for the visuals.

Create this meaure for to use as a filter for the visual (as we did in the previous example):

Filter selected dates =
VAR _minSel =
    CALCULATE (
        MIN ( 'Date SLicer'[Sel Date] ),
        ALLSELECTED ( 'Date SLicer'[Sel Date] )
    )
VAR _Monday =
    CALCULATE (
        MAX ( 'Date Table'[Date] ),
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date] <= _minSel
                && 'Date Table'[DayOfWeek] = 1
        )
    )
VAR _MaxDate =
    CALCULATE ( MAX ( 'Date SLicer'[Sel Date] ), ALLSELECTED ( 'Date SLicer' ) )
RETURN
    IF (
        AND (
            MAX ( 'Date Table'[Date] ) >= _Monday,
            MAX ( 'Date Table'[Date] ) <= _MaxDate
        ),
        1
    )

New file attached

[esarel]

Hello, I try this, but at some months the last day it isn't the last. For example, in your pbi august is only for 29 days. And for example december has null dates.

[PaulDBrown]

I checked and I'm getting 31 days for August. You are right about December, though: it's because the 31st. returns the 1st of January of the current year. Let me find the solution and I'll get back to you.

[esarel]

With the modifications of DaysOfmonth and SortDOM like I say to you in the message below it works properly.

[esarel]

Hi @PaulDBrown , I have managed to solve the December blanks so that it reaches until the 31st, I have modified DaysOfmonth and SortDOM, leaving them like this:

DayOfMonth = 
VAR _SelWeek = 'Date Table'[Week]
VAR _PrevYear = 'Date Table'[Year] -1
VAR _YEAR = 'Date Table'[Year]
VAR _DayOfWeek = 'Date Table'[DayOfWeek]
VAR _Day = DAY('Date Table'[Date])
VAR _Calc = CALCULATE(MAX('Date Table'[Date]), 
                FILTER(ALL('Date Table'),
                'Date Table'[DayOfWeek] = _DayOfWeek 
                && 'Date Table'[Week] = _SelWeek 
                && 'Date Table'[Year] = _PrevYear))
VAR _AuxCalc = CALCULATE(MAX('DATE TABLE'[DATE]), 
                FILTER(ALL('DATE TABLE'),
               'DATE TABLE'[DayOfWeek] = _DayOfWeek 
                && 'DATE TABLE'[Week] = 1
                && 'DATE TABLE'[Year] = _Year)) 
VAR _RES =  IF(not(ISBLANK(_Calc)), _Day & "(" & DAY(_Calc) & ")",if(not(isblank(_AuxCalc)), _Day & "(" & DAY(_AuxCalc) & ")",blank()))
RETURN
CALCULATE(
    _RES, ALLEXCEPT('Date Table','Date Table'[Date]))

Sort DOM =
VAR _SelWeek = 'Date Table'[Week]
VAR _PrevYear = 'Date Table'[Year] - 1
VAR _YEAR = 'Date Table'[Year]
VAR _DayOfWeek = 'Date Table'[DayOfWeek]
VAR _Day =
    DAY ( 'Date Table'[Date] )
VAR _Calc =
    CALCULATE (
        MAX ( 'Date Table'[Date] ),
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[DayOfWeek] = _DayOfWeek
                && 'Date Table'[Week] = _SelWeek
                && 'Date Table'[Year] = _PrevYear
        )
    )
VAR _AuxCalc = CALCULATE(MAX('DATE TABLE'[DATE]), 
                FILTER(ALL('DATE TABLE'),
               'DATE TABLE'[DayOfWeek] = _DayOfWeek 
                && 'DATE TABLE'[Week] = 1
                && 'DATE TABLE'[Year] = _Year)) 
VAR _RES =  IF(not(ISBLANK(_Calc)), DAY('DATE TABLE'[DATE]) *100 + DAY(_Calc),if(not(isblank(_AuxCalc)), DAY('DATE TABLE'[DATE]) *100 + DAY(_AuxCalc),blank()))

RETURN
    CALCULATE ( _RES, ALLEXCEPT ( 'Date Table', 'Date Table'[Date] ) )

With this and without putting any filter and using date table as a selector I have managed to show from 1 to the last day of all the months. The previous days keep failing me, it's not starting on monday...

[PaulDBrown]

Not sure if you know this,  but even if you have the unrelated Date Slicer, you can synch it with the regular date slicer (and then hide it) so other visuals work with the regular date slicer

[esarel]

Hi @PaulDBrown ,

I have a question, if I would like remove months and week for the graph I can? If I do it the graph starts at 1 of th month...

[PaulDBrown]

the reason you are "seeing" the graph starting at the first of the month is because if you remove both the week and month from the graph, the "day(prev day)" values are aggregated if they are the same from one month to the next. In other words, if the date reference value for both June and July include 28(29), the values will be aggregated. 

So either you keep the week or month fields in the graph, or you need to include a specific week or month reference in the date(prev date) field, such as "28(29) -June" or "28(29)-wk 28". (which is the same as including the month or week field anyway....)

Make sense?

[esarel]

Oh Ok, I understand.

And the last question, I know you can synch the selectors with other pages. But I don't know how Can I put date slicer (of Data table, it would be visible) and another data slicer (of Data Slicer it would be hidden) and when I select one of selector of Data table can change the selected value of Data Slicer, it is possible?

I would like this to not change the rest of the graphs on the report page.

[PaulDBrown]

At what level of  granularity do you need the Date Table slicer?

[esarel]

Hello,

I would like, as before, two selectors, one for the months and the other for the years.

These visible selectors that were from Data table and then other hidden from Data Slicer table, the same selectors, for the measure "filter selected dates." And let them be in sync.

[PaulDBrown]

Ok, then add the slicers from the Date Table. Select the year one, and under "View" in the ribbon select Synch Slicers. Go into advanced and enter a name for the group. I've called mine "SynchYear".

Then give the selection slicer the same name.

Do the same for the month slicers. (with a different name). The slicers are now in synch.

Now hide the selection slicers in the "Selection" options under "View" in the ribbon:

Under "Format" -> "Edit Interactions" in the ribbon, turn off the interactions between the Date Table slicers and the visual showing the data for current and previous year week data.

and you will get

[PaulDBrown]

I'm not sure what you need to show in the visual but to get the value for the same day of the same week in  the previous year, you  can try:

With this date table and sample data:

Date TableFact Table

Same Week Previous Year =
VAR _SelWeek =
    MAX ( 'Date Table'[Week] )
VAR _PrevYear =
    MAX ( 'Date Table'[Year] ) - 1
VAR _DayOfWeek =
    MAX ( 'Date Table'[DayOfWeek] )
RETURN
    CALCULATE (
        [Sum Sales],
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[DayOfWeek] = _DayOfWeek
                && 'Date Table'[Week] = _SelWeek
                && 'Date Table'[Year] = _PrevYear
        )
    )

[esarel]

Hi, thanks for your response!

But leap years are not taken into account, I think.

[PaulDBrown]

Have you tried?

The measure looks at the same day of the week of the same week (number) from the previous year (in other words, the month and date are not taken into account) 

[esarel · Accepted solution]

Hi, @PaulDBrown 

I tried your solution. You were right, it does count leap years.

But I am new to this, my final visualization is a graph where the X axis must have the days of a specific month and only compare Mondays with Mondays, Tuesdays with Tuesdays, etc. of a previous year.
For this reason I think that this solution is not valid for me, because I would not know how to put that data on the x-axis.

I do not know if I explain myself very well...