cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Super User

## Problem with granularity

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...

14 REPLIES 14
Post Prodigy

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

Super User

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

Post Prodigy

Hey @mlsx4 ,
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

Super User

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

Post Prodigy

Maybe you can show a bit more of your data model or describe your case (especially the part with real rate) a bit more.

Super User

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 EmployeeName 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

Post Prodigy

@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.

Super User

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

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

``````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]))))

``````Ratio totales =

SUMMARIZE ( 'Plantilla', 'Plantilla'[Employee code],'Plantilla'[% Jornada]),

Post Prodigy

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?

Super User

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 😊

Post Prodigy

Hey @mlsx4 ,
something like this?

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

Regards

Super User

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

Post Prodigy

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

Super User

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors