Assigning unique identifiers to shifts that also passes a date (midnight) for monthly reports.

[KongKlas]

I have a highly advanced question I cannot seem to work around, so this is not for the faint of heart, and for those of you giving this a shot: thank you. It's also quite long, sorry, but it's complex.

Background:
I'm creating reports of how many packages that have been stacked on a machine per shift  - through three shifts. I want the user to open a report on Day 0, and see the numbers from the last three shifts, the latest shift (#3) ending 06:00 the same day. Comparing the three shifts against eachother is of course a goal as well. 

So: one shift (Night) passes midnight (22:00 – 06:00). This is not the question I have at hand, but I'll must show you how I solved this before I continue:

The shifts (and hours) is like this:

Shift 1 (Early) = 06:00 – 14:00

Shift 2 (Late) = 14:00 – 22:00

Shift 3 (Night) = 22:00 – 06:00

Visually it looks like this:

So when the user opens the report (on Day 0), I want to show him/her the "number of stacked articles" from Shift 1, 2 and 3 the day before (marked in green). I know I'm repeating niformation here, sorry. The way I solved the date-shift was splitting up the shifts in a calculated column, and further: the night shift into two parts and got:

• Shift 1
• Shift 2
• Shift 3 Part 1
• Shift 3 Part 2

The DAX for this:

ShiftSplit =

    SWITCH(TRUE(),

        StackerAnt[Time] >= TIME(06, 0, 0) && StackerAnt[Time] < TIME(14, 0, 0),"Shift1",

        StackerAnt[Time] >= TIME(14, 0, 0) && StackerAnt[Time] < TIME(22, 0, 0),"Shift2",

        StackerAnt[Time] >= TIME(22, 0, 0) && StackerAnt[Time] < TIME(00, 0, 0),"Shift3_Part1",

"Shift3_Part2")

A beautiful column arised and I then created some variables to fetch all the shifts from (–1) days ago, except the Shift3_Part 2, which I said: "fetch todays values from this". Then I summed these four values together to get the number of stacked packages from yesterday three shifts.

Again, more DAX:

LastDayLastShifts =

VAR Shift1_Yesterday =

    CALCULATE(

        COUNT( StackerAnt[License] ),

            StackerAnt[ShiftSplit] = "Shift1", DATEADD ( CalendarRolling[Date], -1, DAY )

    )   /*Gets the number of packages produced from Shift 1 Yesterday*/

VAR Shift2_Yesterday =

    CALCULATE(

        COUNT( StackerAnt[License] ),

            StackerAnt[ShiftSplit] = "Shift2", DATEADD( CalendarRolling[Date], -1, DAY )

    )   /*Gets the number of packages produced from Shift 2 Yesterday*/

VAR Shift3_Part1_Yesterday =

    CALCULATE (

        COUNT ( StackerAnt[License] ),

            StackerAnt[ShiftSplit] = "Shift3_Part1", DATEADD ( CalendarRolling[Date], -1, DAY )

    )   /*Gets the number of packages produced from Shift 2 part 1 yesterday*/

VAR Shift3_Part2Today =

    CALCULATE (

        COUNT ( StackerAnt[License] ),

            StackerAnt[ShiftSplit] = "Shift3_Part2", DATEADD ( CalendarRolling[Date], 0, DAY )

    )   /*Calculating the amount of licenses produced from Shift 2 part 2 yesterday*/

VAR LastShift = Shift1_Yesterday +

                Shift2_Yesterday +

                Shift3_Part1_Yesterday +

                Shift3_Part2Today

    /* Combining all the shifts, adjusted for dateshift */

RETURN

    LastShift

So far so good, right? But this is where I might have flown to close to the sound, because I want to not only create daily reports of this, but also weekly and monthly reports – and I have (almost) absolutely no idea how to continue, in regards of Power BI!

But.. I will ignore the monthly report for now, and humbly ask for the weekly report "solution" - or just a hint, inspiration, anything! Anything more than my brain can produce at this moment would be amazing. I have put so much effort into this, and I cannot stop now. From my amazing excel-skills, I have drawn yet another visual of my thoughts regarding weekly report design.

I think (?) it's a good idea to assign unique Shift ID's to each shift, starting at 1 every week. That way, I can filter on shift ID's by week number, and later on, on the montly report, I can also sum ShiftID 1 to 21 from week n to week n+3. So please; correct me here, because this I am highly unsure of this part. It was actually a LEAN-consultant that sent me down this part, so I'm not taking credit/blame for this last idea..

To sum up my questions:

1. Is it a good idea to create Shift ID's on a weekly basis, or should I stick to monthly? Or perhaps yearly?!
2. Where in the process, and more precisely HOW, do I assign Shift ID's?
3. If the answer to question 1 is "no", then ignore question 2 and tell me how you would have solved this instead.

Here's a visual of my monthly report as well:

I've never seen a post this long, and I've never written a post before either, but that's because I'm 'really good' at googling 🙃. So again, I'm sorry if I've wasted anyones time, but I do think the answer to this, as well as the results I've allready have come up with, can have good value for other people as well. 

Brg. Niklas

[v-stephen-msft]

Hi @KongKlas ,

It‘s recommend that you try to create measures instead of calculated columns. Because if you have a measure, you can directly add the month column to the visual where the measure is located, and the row context will help you dynamically filter the results group by months.

Measure vs Calculated Column: The Mysterious Question? Not! - RADACAD

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.