Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I have a fairly straight forward model with three fact tables and few dimensions. One of the dimension is a version table that contains if the row is an actual or a forecast version. Those two tables are related on an id. I´m trying to create a measure that has the following conditions:
If the chosen month is less than last forecast month (5 at the moment) then use actuals otherwise use the current forecast.
I have managed to create the measure that works well unless you choose multiple months and one of the month falls on each side of the condition above. So say you choose JAn, Feb, Mar, Apr and May 2019 the measure should show you actuals for the first four and forecast for the last one. Again my measure works when looking at individual months but the total does not give me the correct results. I know why it isn´s as I´m using MAX(selected month) to see if the selected month should be actual or forecast which works fine when looking at individual months but will give you May when looking at the total and therefore the total is giving me all forecast.
CALCULATE(SUM(ExFactorySales[Net Sales Value EUR]),FILTER('Version','Version'[VersionKey]=IF(MAX('Calendar'[Year Code])<IF(MONTH(TODAY())=1,YEAR(TODAY())-1, YEAR(TODAY())),"ACTUAL",IF(MAX('Calendar'[Month Number])<IF(MONTH(TODAY())>9,10,IF(MONTH(TODAY())<5,1,5)),"ACTUAL","Current Forecast"))))
The above is my measure with the part in red as the problem area. Before anyone asks I also have it with variables as well but just wanted to put it in here as a simple measure.
I tried using SUMX like below to iterate through the months and then sum them but since the Calculate is evaluating the whole thing the MAX is still giving me the same issue
SUMX( VALUES('Calendar'[Cut -off Month]), CALCULATE(SUM(ExFactorySales[Net Sales Value EUR]),FILTER('Version','Version'[VersionKey]=IF(MAX('Calendar'[Year Code])<IF(MONTH(TODAY())=1,YEAR(TODAY())-1, YEAR(TODAY())),"ACTUAL",IF(MAX('Calendar'[Month Number])<IF(MONTH(TODAY())>9,10,IF(MONTH(TODAY())<5,1,5)),"ACTUAL","Current Forecast")))))
Can anyone help me here? As a last resort I will prevent them from selecting multiple months but that would not be ideal.
I have tried all kinds of aggregations for the month number but none of them works. SelectedValue gives you nothing evaluating the total as there are 5 months in context.
Ásgeir
Why don't you create a column in your fact table that will hold the right amount for each row? Once you have it, the measure you want will be a simple SUM over the column... No conditional logic required. All you have to do is to create the right logic in your calculated column.
Best
Darek
Hi Darek
I did think about that but I have around 20 versions in my real data. Each month might have multiple versions so I cannot add the correct amount for each row and then sum it as the there would be multiplications.
Example:
I would get 1350 if I sum the Correct amount column
Hi Stachu
Here is more context. As I said in my orginal message the calculation is correct on the month level but wrong on the total level. You can see in the image below more clearly what the structure is and what the problem is. You can also download a simplified version of the pbix and and excel file that is it's source from here
I think you should use 'Calendar'[Cut -off Month] as the reference point, instead of TODAY()
Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
Column1 | Column2 |
A | 1 |
B | 2.5 |
What are the relationships between 'ExFactorySales', 'Version' and 'Calendar'?
if there is no overlap in your data between "ACTUAL" and "Current Forecast", ,i.e. for each month there is either a actual figure or forecast figure (but never both) then this should work:
Measure = CALCULATE ( SUM ( 'ExFactorySales'[Net Sales Value EUR] ), 'Version'[VersionKey] = "ACTUAL",
--'Calendar'[Month Number] <= MAX('Calendar'[Cut -off Month]) ) + CALCULATE ( SUM ( 'ExFactorySales'[Net Sales Value EUR] ), 'Version'[VersionKey] = "Current Forecast"
--'Calendar'[Month Number] > MAX('Calendar'[Cut -off Month]) )
if there is overlap, then you can uncomment filter on the Calendar table, but I cannot be sure it works without having more info on your model (but I think you get the general idea)
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |