Compare Weekday and Weekend Results versus previous years - Actual and YTD numbers - Power BI

ricafonyat · March 20, 2018, 10:56pm

Hi Sam, how are you?
I hope all is well with you…

Let me know if you can help me with a solution.

In my pivot table there are basic measures:

Sales
LY Sales
YTD Sales

But there is a slicer too with two options: Weekdays & Workdays
(In my date table there is a text column with these two options - where my slicer come from)

When I use one of these filters above, the results of LY & YTD measures are wrong.
My idea is to compare weekdays sales x LY weekdaysales and so on…

Have you got any tip for me to solve it?

Best Regards
Jose Ricardo

sam.mckay · March 20, 2018, 11:15pm

Can you post images please. Too hard to image what’s happening and what you exactly need.

Need to see currrent formula and preferably what they are calculating in a table

sam.mckay · March 20, 2018, 11:26pm

This video may assist

ricafonyat · March 21, 2018, 1:01am

Sam, here are the measures:

Room Revenue = 
      CALCULATE(sum(FutureBookings[Revenue])
       +
      CALCULATE(SUM(PastBookings[Revenue]);
         FILTER(StatusReservation;
         StatusReservation[IdStatus] = 1 || StatusReservation[IdStatus] = 2 || StatusReservation[IdStatus] = 3 || StatusReservation[IdStatus] = 4 || StatusReservation[IdStatus] = BLANK()
                                )
                            ))


LY Room Revenue = 
VAR DiaCorrente =
    LASTNONBLANK (Dates[Date]; [Room Revenue])
VAR PeriodoCorrente =
    DATESBETWEEN ( Dates[Date]; MIN(Dates[Date]);DiaCorrente)
VAR PeriodoCorrenteAnterior =
    SAMEPERIODLASTYEAR ( PeriodoCorrente )
RETURN
    IF (
        DiaCorrente >= MIN ( Dates[Date]);
       CALCULATE([Room Revenue]; PeriodoCorrenteAnterior ))

YTD Room Revenue V3 = 
    CALCULATE([Room Revenue];
        FILTER(all(dates);
            Dates[Year]=max(Dates[Year]) && Dates[Date] <=MAX(Dates[Date])
            )
    )

Prints:

As you will notice in this last picture (Workday filtered) my LY measure is not working.

Is there a way to fix it?
Tks.

sam.mckay · March 21, 2018, 4:18am

I believe the reason it’s doing this as you are using time intelligence calculations and the LY calculation is retrieving the exact same day the previous year regardless if it’s a workday or not.

The reason it’s likely doing this is because time intelligence formulas work over an interior date table (virtual one) not the actual date table, so the formula doesn’t know about workdays/weekend and is just going on the date context it’s in.

I actually have not run into this myself. I’ll do some testing, but this is likely the problem from what I can see.

sam.mckay · March 21, 2018, 4:27am

I’ve tested myself and get exactly the same results. It must be because of what I mentioned above.

Are you actually look to solve the results for the first workday in one year versus the first workday in a particular day the next year.

You will likely have to not use time intelligence here, and use different formulas.

I first want to make sure this is what you are needing.

Sam

Paul · March 21, 2018, 10:01am

Hi,
I read something about virtual internal date tables. Allegedly by using the “Mark as Date Table” option in the ribbon for your date table, said effect will be eliminated. Have not tested this.
Paul

ricafonyat · March 21, 2018, 11:35am

That´s right Sam !

I am just needing to compare results or the first workday in one year versus the first workday in a particular day the next year/last year. The same will be valid for weekend days.

So that the measures that I am looking can not be based on time intelligence functions.

Tks
Jose Ricardo

sam.mckay · March 21, 2018, 8:39pm

Not sure about this one, didn’t solve it for me.

ricafonyat · March 21, 2018, 9:09pm

Hi Sam!

I am trying to find the solution, but so far nothing.

I am sure you are going to find it.

Looking forward to hearing from you soon
Best Regards

sam.mckay · March 21, 2018, 9:22pm

Working on this, will come back shortly. I think I have the solution, just checking it.

sam.mckay · March 21, 2018, 10:21pm

Ok this isn’t actually so easy but here the solution to get you going.

See the below table breaks out the workday and weekend numbers

Here’s the formulas for that

Workday Number = 
VAR CurrentMonth = SELECTEDVALUE( Dates[Month & Year] )
VAR MonthTable = FILTER( ALL( Dates ), Dates[Working Days] = "Weekday" && Dates[Month & Year] = CurrentMonth )

RETURN
IF( SELECTEDVALUE( Dates[Working Days] ) <> "Weekday",
    BLANK(),
        RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayOfMonth] ) ), , ASC ))

Here’s the weekend one

Weekend Number = 
VAR CurrentMonth = SELECTEDVALUE( Dates[Month & Year] )
VAR MonthTable = FILTER( ALL( Dates ), Dates[Working Days] = "Weekend" && Dates[Month & Year] = CurrentMonth )

RETURN
IF( SELECTEDVALUE( Dates[Working Days] ) <> "Weekend",
    BLANK(),
        RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayOfMonth] ) ), , ASC ))

Then we get to the hard part.

We need to jump back one year and find the corresponding workday date. This formula works as you can see

Here’s the formula

Sales LY Weekday = 
VAR CurrentWorkday = [Workday Number]

RETURN
IF( SELECTEDVALUE( Dates[Working Days] ) <> "Weekday",
BLANK(),
SUMX( FILTER(
    SUMMARIZE( ALL( Dates ), Dates[Date], Dates[Year], Dates[MonthOfYear], "Working Day", [Workday Number] ),
        Dates[Year] = MIN( Dates[Year] ) - 1 &&
        Dates[MonthOfYear] = MIN( Dates[MonthOfYear] ) &&
        [Working Day] = CurrentWorkday  ),
    [Total Sales] ))

To get the weekend would just need slight adjustment to this.

See how you go with these and the logic here.

sam.mckay · March 22, 2018, 11:42pm

How did you go with my solution.

This was a really tough challenge, but think the solution is a really good one.

Let me know.

Thanks

ricafonyat · March 23, 2018, 3:24am

Sam, first of all tks a lot for yours precious help and tough efforts to find a solution/way for me. Secondly to tell you - to be honest - I haven´t got the time and peace to test your measure, but I will do it during this weekend.
As you know and teach us, besides the DAX stuff, the visual part of the model is very important too and this is what I have spent the last hours working on.
Tks
José Ricardo

sam.mckay · March 23, 2018, 6:31am

Ok great, no problem.

sphayward · May 30, 2018, 5:10am

Can I just say as a new member that this post was extremely helpful in me starting to solve an issue around comparing and counting working days in a month for our B2B wholesale distribution company just embarking on a Power BI implementation project.

However, I feel like I need to add a working day index as a calculating column or query to get maximum use. Sam - I’m not sure if you will see this but would you be able to provide any thoughts on how to implement a column to put in the working day in the month into a date table (I’ve also also built public holidays and company shutdowns into mine) ? I’ve been trying for days and still can’t get there as a new Power BI user. Our company does a lot of YOY and cumulative (running total) comparisons and the measure of working days demonstrated here doesn’t quite seem to allow me to do all the calculations I need. Thanks in advance. Simon

sam.mckay · May 30, 2018, 7:22am

Yes will test this out and post here.

sam.mckay · May 31, 2018, 2:31am

Try these, they should create what you need

Day Type = IF( AND( Dates[DayInWeek] <> 6, Dates[DayInWeek] <> 0 ), "Workday", "Weekend" )

Workday Number = 
VAR CurrentMonth = Dates[Month & Year]
VAR MonthTable = FILTER( ALL( Dates ), Dates[Day Type] = "Workday" && Dates[Month & Year] = CurrentMonth )

RETURN
IF( Dates[Day Type] = "Weekend",
    BLANK(),
        RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayOfMonth] ) ), , ASC ))



Weekend Number = 
VAR CurrentMonth = Dates[Month & Year]
VAR MonthTable = FILTER( ALL( Dates ), Dates[Day Type] = "Weekend" && Dates[Month & Year] = CurrentMonth )

RETURN
IF( Dates[Day Type] = "Workday",
    BLANK(),
        RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayOfMonth] ) ), , ASC ))

Place these all in calculated columns and it should all work fine.

Chrs

sphayward · June 1, 2018, 2:56pm

Thanks for the assistance Sam … I can now create the calculated column for “Working Day” reference within a month but am now struggling to reference the previous years equivalent data from the same working day but will persevere and let you know if I need more support … Regards,

Simon

sam.mckay · June 3, 2018, 11:41pm

Sure just see if you can work out what you need and post where you get to if you can’t work it out.

What you will like need to do it use non time intelligence functions to get this working.

Here’s a few examples of the techniques you’ll need to use.