SamperiodlastYear Working Days

Neville · June 8, 2020, 5:22pm

I have a measure to calculate the current working days, based on the current context, and this works fine as its pretty straight forward. The issues comes when I want to calculate the working days using the SAMEPERIODLASTYEAR Function, which is not giving the correct values.

As you can see from the table below, the Measure for “Working Days Same Period LY” is not give the correct value. The number of “1’s” is actual 50, but the total value is given 65.

Working Days = sum(‘Date’[isWorkday])

Working Days Same Period LY = CALCULATE([Working Days], SAMEPERIODLASTYEAR(Date’[Date]))

I’ve tried a number of different combinations, and whilst the current context working days is already calculated correctly, the SAMEPERIODLASTYEARS is not given the correct value. The value should be 50, but its giving 65.

isWorkday is a Calculated Column

isWorkday = if(‘Date’[WeekdayNum]<=4,1,0)

I’ve also tried CountRows, and this also gives the wrong result, and many other DAX Functions

Working Days = CALCULATE(COUNTROWS(‘Date’), FILTER(ALLSELECTED(‘Date’), ‘Date’[WeekdayWeekend]=“Weekday”))

Working Days = Sumx(
FILTER(ALLSELECTED(‘Date’),
‘Date’[Date] >= min(‘Date’[Date]) &&
‘Date’[Date] <= max(‘Date’[Date])
),
‘Date’[isWorkday]
)

I’m clearly missing something, and is most probably staring be straight in the face, but after wasting 3 hours of my life on this, I just cannot see it, as all the above combination of Working Days gives the same result for Working Days Same Period LY

Thank you
Neville

Nick_M · June 8, 2020, 5:34pm

@Neville
What about something like this:

CALCULATE(
    COUNTROWS( 'Date' ),
        FILTER( ALL('Date'),
            [MaxDate] <= 'Date'[Date]
            && [MinDate] >= 'Date'[Date]
            && 'Date'[IsWorkDay] =1
        )
)

Not 100% sure that will work as expected, but the best I can do without seeing some data.
-Nick

Neville · June 8, 2020, 5:51pm

the MinDate, MaxDate Columns were only given to show the current context. I will not be using these going forward, as I’m looking to get a single value and only created the table to demonstrate the issue with SAMEPERIODLASTYEAR

But I did try using your solution, and still get 65 days for SAMEPERIODLASTYEAR.

Neville · June 8, 2020, 6:28pm

SamePeriodLastYear-WorkingDays.pbix (53.0 KB)

The attached file, I’ve mocked up which demonstrates the issue I’m having.

The calculation should return a value of 50, but its given 65.

Neville · June 8, 2020, 7:46pm

So I had to created a new measure to fix the incorrect total given by Working Days Same Period LY

Correct Value = if(HASONEVALUE(‘Date’[isWorkday]),
[Working Days Same Period LY],
SUMX(VALUES(‘Date’[isWorkday]), [Working Days Same Period LY]))

Now I’m getting the correct value, though I’m not 100% clear why I have to do this.

So I’m closing this as address by myself.

JBright · June 8, 2020, 9:18pm

Hi @Neville,
The time intelligence functions in PowerBI will not work properly without a complete date table.
I added a full date table as provided Here

I then added a column for working days ( does not consider holidays) to the calendar.
Once that was done, I was able to add the below formulas:
WD_Revised =
CALCULATE(COUNTROWS(‘Calendar’),
FILTER(VALUES(‘Calendar’[IsWorkday]),‘Calendar’[IsWorkday] =1)
)

LY_WD =
CALCULATE([WD_Revised],
SAMEPERIODLASTYEAR(‘Calendar’[C_Date])
)

And then to test if I was getting the correct results
WD_Between =
CALCULATE([WD_Revised],
DATESBETWEEN(‘Calendar’[C_Date],“04/01/2019”,“06/08/2019”)
)

Below are my results:

My sample file SamePeriodLastYear-WorkingDays__Revised.pbix (151.6 KB)

I hope this helps.

Jamie

Neville · June 8, 2020, 9:45pm

Hi Jamie,

Thank you for this, but I’m not clear an what you mean by Full Date Table, as my date table covered the full range and context that I was working in, and I needed no additional dates / columns in my date table. I was working within the current context for 01.APR.20 - 08.JUN.20 , which means for SAMEPERIODLASTYEAR I need to have dates covering 01.APR.19 - 08.JUN20, which was there in my date table.

This solution I provided, addressed my immediate issue, but thank you for taking the time to respond, but I don’t think this is the solution to the underlying issue I had shared.

Kind regards
Neville

JBright · June 8, 2020, 9:54pm

Hi again Neville,
Well, I am no expert, that’s why I am here. This was actually the first question I had the courage to try and answer. Maybe I’ll regret that.

My understanding of time intelligence is that all dates for the covered years must be present in the dates table. In your case, 1-12019 through 12-31-2020. Once I installed the full date table, the resulting calculations were not very difficult and arrived at the correct number of 50 days for the period 1 Apr 20 through 8 June 20.

Hopefully one of the experts here will chime in and correct me if I’m wrong as I certainly don’t want to mislead you.

Regards,
Jamie

Rajesh · June 8, 2020, 11:44pm

Hi @Neville

While creating measures we’ve to consider the context… Here in this case
Grand Total will not give correct values… because there is no filter context from the visual at that particular row, so we have to change the logic using HASONEVALUE OR ISFILTERED functions in if condition to get the correct results.

Neville · June 8, 2020, 11:58pm

Hi Jamie,

It was an Excellent response, just that you saw my dates as 4th January 2019 instead of 1st April 2019, which is why you went down the route of not have a full and valid date table.

Thanks
Nev

Neville · June 8, 2020, 11:59pm

Yes, Rajesh this is what I eventually worked out. This is one of the area’s as keep forgetting about and why I posted my solution above, once I worked it out.

All the best
Nev

Nick_M · June 9, 2020, 8:31am

@Neville,
I think if you used DATESBETWEEN (like @JBright mentioned) combined with using SAMEPERIODLASTYEAR and FIRSTDATE/LASTDATE you will get the output you are expecting, well hopefully

Previous Working Days = 
/*Variables to define what the previous start and end dates are*/
VAR _PrevStart  = SAMEPERIODLASTYEAR( FIRSTDATE( 'Date'[Date])) --Using the Current Filter context, goes back one year from the start
VAR _PrevEnd    = SAMEPERIODLASTYEAR( LASTDATE('Date'[Date]))   --Using the Current Filter context, goes back one year from the end

/* Number of days between the start and the end, used to check if our calendar has dates that go back that far*/
VAR _StartEnd   =   INT( _PrevEnd - _PrevStart )

RETURN


IF(
    NOT ISBLANK(_StartEnd),                                 --Checks to see if there are days between the start and end. The NOT turns True into False and False into True
     CALCULATE( 
         [Working Days]                                     --Previous defined measure that has the logic to count the # of working days
         ,DATESBETWEEN( 'Date'[Date], _PrevStart,_PrevEnd)  --Use DATESBETWEEN to modify the current filter context. Use the start/end from the above variables
 )
)

in your data set we had complete days for April and May of FY21, which we would then want the full month of working days previously:

I believe the issue you were running into was that in June FY21, there was only data from 6-1 to 6-8 but you were getting the full amount of June FY20 working days. Using the _PrevStart and _PrevEnd variables will account for that, and only give you the total number of working days from the start and end of the current filter context:

Neville · June 9, 2020, 6:33pm

Great Job Nick, better that creating another measure using HASONEVALUE, which is the solution I have in production right now.

Nev

EnterpriseDNA · June 16, 2020, 4:00am

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Group. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!