Forum Discussion
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
Yes, but with a small detail, imagine that on 09/06 theoretical rate changes... Would it work for the new rates?
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 😊
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?
RegardsHey 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
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.
