Problem with granularity

[mlsx4]

Hello everyone!

I have a problem that I cannot figure out how to solve. 

Let's say I need to compute the difference between my theoretical rate and my real rate. Therefore I have a table for my theoretical rate:

*Notice that I have different categories, different centers (11, 12, 13) but also companies (A, B).

Cod	Theoretical rate	Category	YearMonth
11A	8,73	A	2023-Junio
11A	18,08	B	2023-Junio
11A	2,62	C	2023-Junio
12A	2,62	A	2023-Junio
12A	0,38	B	2023-Junio
12A	1	C	2023-Junio
11B	2,62	A	2023-Junio
11B	0,5	B	2023-Junio
11B	0,5	C	2023-Junio

This theoretical rate should keep for next months/ years until there is some change in the value (could be a year later, in a month,... There is no defined change time).

My problem is that I don't know how to compute the difference using the currently active value. For instance, if I am in August these values are still effective. However, and because of the YearMonth it is only computed for June.

All of these, being aware of the corresponding center / company...

[sergej_og]

Hi @mlsx4 ,
I tried different approaches for this case..and hope I catched it right concersing your discription.
I had to tweek a bit with model and measures.

Actually I get following result:

 - Assuming real rate data comes daily and theretical rate data random -> Real Rate table is my Fact table
 - LookUp for theoretical rates (calculated column)
 - works for each single Code (conserning your Screen above)
-----------------------------------------
Is this about the expected result?

Regards

[mlsx4]

Hi @sergej_og 

That's what I need!! How can I achieve it?

[sergej_og]

Hey @mlsx4 ,
glad to hear.
I will try to leave you an understandable description here.
-------------------------------------------------
1. I created a calculated column in "real rate" table (my fact table) to fetch theoretical rates.
Result:

Formula:

LookUp_Theo_rate = 
   LOOKUPVALUE(
      'Theo rate Table'[Theor. Rate], 'Theo rate Table'[YearMonth], 'REAL rate Table'[YearMonth],
      'Theo rate Table'[Code], 'REAL rate Table'[Code],
      'Theo rate Table'[Category], 'REAL rate Table'[Category])

 
Can you pls try to achieve similar result.

When I drop these 2 fields into my table it looks like this:

2. To fill the gaps I used this formula:

Last non blank Theo_rate = 
VAR Last_non_blank_date = 
        CALCULATE(
            MAX('REAL rate Table'[YearMonth]),
            FILTER(
                ALL('REAL rate Table'), 'REAL rate Table'[YearMonth] <= MAX('REAL rate 
                Table'[YearMonth]) && 'REAL rate Table'[LookUp_Theo_rate] <> BLANK() )
            )    //this part will give you the MAX non blank date

VAR Last_non_blank_Value =
        CALCULATE(
            SUM('REAL rate Table'[LookUp_Theo_rate]),
            FILTER(
                ALL('REAL rate Table'), 'REAL rate Table'[YearMonth] = Last_non_blank_date), 
            FILTER(
                ALL('REAL rate Table'), 'REAL rate Table'[Code] = SELECTEDVALUE('REAL rate Table'[Code])),
            'REAL rate Table'[Category] = SELECTEDVALUE('REAL rate Table'[Category])
            )

RETURN
    IF(
        HASONEVALUE('Calendar'[Date]) &&
        [Theor_] <> BLANK(),
        [Theor_],
        Last_non_blank_Value)

Try to apply this piece of code into your model.
I cross my fingers.

I hope I could transfer this well to your case.
This formula gave me this result:

----------------------------
Regards

[mlsx4]

Hi @sergej_og 

Thank you. You have explained perfectly. I will try, because Real Rate is not a table... It is calculated 😪 The calculus is a real headache

[sergej_og]

Maybe you can show a bit more of your data model or describe your case (especially the part with real rate) a bit more.
How do you get your real rates into your model?

[mlsx4]

Hi @sergej_og 

I'm gonna try to explain it:

• I have a dim table for centers with codes, location, full name...
• I have a fact table with all temporary medical leaves and absences due to holidays, marriages, or some any other reason
• I have a dim table with all the staff
• Finally, I have a calendar table

Then:

Sample of absences table would be:

Type of absence	Subtype of absence	Employee Code	Employee ID	Employee Name	Starting date	Ending date	Center Key
Absence	Holidays	000001	98765431A	Juan López	01/06/2023	07/06/2023	11A
Medical leave	Accident	000015	43275618Y	David Fernández	08/06/2023		11A
Absence	Marriage	000002	12345679B	María Martínez	04/06/2023	19/06/2023	11A

Sample of Staff table will be:

Employee Code	Full name	Working Category	Contract category	Employee % of hours	Starting date	Ending date	Center Key
000001	Juan López	A	Full time	100	07/05/2017		11A
000002	María Martínez	B	Temporary	75	01/06/2023	25/06/2023	11A
000015	David Fernández	C	Partial-time	89,74	01/05/2023	31/12/2023	11A

So, with all this information what I'm doing to calculate the rate of people is:

• First, I compute if a person is on holidays or in a medical leave. Put a 0 if it is, or a 1 if not.
• Then, I compute if a person has an active contract. Put a 0 if it isn't or a 1 if it is active.
• I use both measures as a multiplier: employees rate * isOnHoliday* isActive, where employee rate is %hours/100
• Then, I summarized by the day

So, for instance Juan will have 0 for days between 1 and 7 of June, while for the rest of the month will give me a rate of 1. María will have 0,75 for days 1 to 3 and 20 to 25. 0 for all the rest. And so on...

Hope it is a little more clear now

[sergej_og]

@mlsx4 
ok, that´s a bit different starting point.
Can you post your calculations for computing "on holidays etc.", "is active etc.".
I will try to reconstruct and understand this.

Maybe a calculated table could be a possible solution.

[mlsx4]

My measures are a bit mess, because I have tried several ways to do it. This way works for all the graphs I need to show (except the theoretical one), but they are not probably the optimum way of doing or even I'm spinning around in circles:

BajasD = 
    CALCULATE(DISTINCTCOUNT('IT/ABS'[Employee name]),
    FILTER('IT/ABS','IT/ABS'[Starting date]<=MAX('Calendar'[Date]) && OR('IT/ABS'[Ending date] >=MAX('Calendar'[Date]),ISBLANK('IT/ABS'[Ending date]))))

//The multiplier
Multiplicador de bajas = IF([BajasD]=1,0,1)

For active contract:

DiaT = CALCULATE(
    SUMX(VALUES('Calendar'[Date]),CALCULATE(DISTINCTCOUNT('Plantilla'[Employee code]),
    FILTER('Plantilla','Plantilla'[Starting date]<=MAX('Calendar'[Date])&& 'Plantilla'[Ending date]>=MAX('Calendar'[Date])))))

//The multiplier
Multiplicador de jornada = IF([DiaT]=1,1,0)

Ratio jornada = 

VAR diasMes= DAY(LASTDATE('Calendar'[Date]))

var num= SUMX(VALUES('Plantilla'[Employee Code]),CALCULATE(SUMX('Plantilla','Plantilla'[% Jornada]),FILTER('Plantilla','Plantilla'[Starting date]<=MAX('Calendar'[Date]))))

RETURN num* CALCULATE([Multiplicador de bajas],USERELATIONSHIP(Employee code],'IT/ABS'[Employee code]))*[Multiplicador de jornada] 

Ratio totales = 

var summarizedTable = ADDCOLUMNS (
SUMMARIZE ( 'Plantilla', 'Plantilla'[Employee code],'Plantilla'[% Jornada]),
"Días", CALCULATE ([Ratio jornada]) )

RETURN SUMX(summarizedTable,[Ratio jornada])

[sergej_og]

Hey @mlsx4 ,
not sure since I have no clue how your datamodel looks like.
Show me your table with your theoretical values before and after change of values.
I don´t know how your data come in/stored in the table.
Do you have a new line for new values or is the value overwritten?

[mlsx4]

Hi @sergej_og 

Theoretical rates are defined in a table by just including a new line and the date they have been changed. That's why I said that it's being a headache.

Following the example, this would be theoretical rates table. For instance, in Jun-2023, category A changes its value:

CenterKey	Category	Theoretical rate	Date
11A	A	8,73	Jun-2022
11A	B	18,08	Jun-2022
11A	C	2,62	Jun-2022
11A	A	9	Jun-2023

Thank you so much for your effort 😊

[sergej_og]

Hey @mlsx4 ,
something like this?

I made a small model following data your provided above (expected output).
Would it work for you.

Regards

[mlsx4]

Yes, but with a small detail, imagine that on 09/06 theoretical rate changes... Would it work for the new rates?

[sergej_og]

Do you have a target result which you can provide here?
Difficult to understand just seeing at your Screenshot and a bit discription.
What is your expected goal?

[mlsx4]

Hi @sergej_og 

This is my expected output. Imagine the top part are the slicers options.

If I do things just for a center (no selectors) I can achieve it without any problem. The thing is that I have all centers in the same file (coded as 11A,11B, 12A...) and the date handicap, since theoretical rates can change from one month to another. 

Also, as you can see granularity in the output is daily, but I only have theoretical rates within a date (the one in which is established).

It has been a headache for myself try to solve it. 

Thank you for trying to help.