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).
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...
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?
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 = 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:
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?
I'm gonna try to explain it:
Sample of absences table would be:
|Type of absence||Subtype of absence||Employee Code||Employee ID|
|Starting date||Ending date||Center Key|
|Medical leave||Accident||000015||43275618Y||David Fernández||08/06/2023||11A|
Sample of Staff table will be:
|Employee Code||Full name||Working Category||Contract category|
% of hours
|Starting date||Ending date||Center Key|
|000001||Juan López||A||Full time||100||07/05/2017||11A|
So, with all this information what I'm doing to calculate the rate of people is:
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 = 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])
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?
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:
Thank you so much for your effort 😊
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?
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.