Showing results for 
Search instead for 
Did you mean: 

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

Super User
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).

CodTheoretical rateCategoryYearMonth


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

Post Prodigy
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?


Hi @sergej_og 


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

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.



LookUp_Theo_rate = 
      '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 = 
            MAX('REAL rate Table'[YearMonth]),
                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 =
            SUM('REAL rate Table'[LookUp_Theo_rate]),
                ALL('REAL rate Table'), 'REAL rate Table'[YearMonth] = Last_non_blank_date), 
                ALL('REAL rate Table'), 'REAL rate Table'[Code] = SELECTEDVALUE('REAL rate Table'[Code])),
            'REAL rate Table'[Category] = SELECTEDVALUE('REAL rate Table'[Category])

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

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:


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

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?

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


Sample of absences table would be:

Type of absenceSubtype of absenceEmployee CodeEmployee ID



Starting dateEnding dateCenter Key
AbsenceHolidays00000198765431AJuan López01/06/202307/06/202311A
Medical leaveAccident00001543275618YDavid Fernández08/06/2023 11A
AbsenceMarriage00000212345679BMaría Martínez04/06/202319/06/202311A


Sample of Staff table will be:

Employee CodeFull nameWorking CategoryContract category


% of hours

Starting dateEnding dateCenter Key
000001Juan LópezAFull time10007/05/2017 11A
000002María MartínezB


000015David FernándezCPartial-time89,7401/05/202331/12/202311A



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

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.

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 = 
    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:

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


Post Prodigy
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?

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:

CenterKeyCategoryTheoretical rate











Thank you so much for your effort 😊

Post Prodigy
Post Prodigy

Hey @mlsx4 ,
something like this?


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


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

Post Prodigy
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.
What is your expected goal?

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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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