Forum Discussion
Measure with time intelligence
Dear all,
I have a problem with some data in my power bi model. I am using AS with a tabular model .bim
I have the 'DWH DimDate'[Date] as calendar
I have as well Time Intellegince with 2 options:
- Month --> CALCULATE( SelectedMeasure() , DATESMTD( 'DWH DimDate'[Date] ))
- YTD --> VAr meses= month (MAX('DWH DimDate'[Date]))
CALCULATE( SelectedMeasure(),
DATESINPERIOD (
'DWH DimDate'[Date],
MAX('DWH DimDate'[Date]),
- meses,
MONTH
))
Is working for each measure properly but the main problem I have is that this measure [MB] take some conditions from January to November and some other from December.
VAR yearA = YEAR(MIN('DWH DimDate'[Date]))
--- MB in NAV: fins 30/11/2023
VAR Order700 = CALCULATE([Total Amount], LEFT('DWH FacAccount'[NumberAccount],3) = "700")
VAR Order600 = CALCULATE([Total Amount], LEFT('DWH FacAccount'[NumberAccount],3) = "600")
VAR MB_LitrosNAV = Order700 + Order600 + Order610 + Order709 + Order706 + Order608 + Order609
---Post NAV, in D365 01/12/2023
VAR Ventas = CALCULATE([Total Amount], LEFT('DWH FacAccount'[NumberAccount],2) = "70", 'DWH FacAccount'[Actividad_code] = "002")
VAR Compras = CALCULATE([Total Amount], LEFT('DWH FacAccount'[NumberAccount],2) = "60", 'DWH FacAccount'[Actividad_code] = "002")
VAR MB_LitrosD365 = Ventas + Compras
RETURN
IF((MONTH(MAX('DWH DimDate'[Date])) = 12 && MIN('DWH DimDate'[Date]) >= DATE(2023, 12, 1) && yearA >= 2023), MB_LitrosD365,
IF((MONTH(MAX('DWH DimDate'[Date])) = 12 && MONTH(MIN('DWH DimDate'[Date])) = 1 && yearA >= 2023),
--MB_LitrosD365,
CALCULATE(MB_LitrosNAV, DATESBETWEEN('DWH DimDate'[Date], DATE(yearA, 1, 1), DATE(yearA, 3, 30))),
MB_LitrosNAV))
When I select individually MONTH, the data is filled properly (in December MB_LitrosD365 and in the other months MB_LitrosNAV) but when i am on DECEMBER selected and then select YTD option, the data shown me the sum from the the values in the first period but until December (MB_LitrosNAV from Jan to Dec)... and should be the SUM of MB_LitrosD365 (december) + MB_LitrosNAV (but from Jan to Nov)
Hope I have explained properly. I could not attach the sample data, sorry.
Hi drivero ,
Here's a revised approach to your problem:
-
Refine Conditional Logic: Ensure that your conditional logic accurately reflects the transition between and . It's crucial that the condition checks not only the month but also aligns with your YTD calculation requirements.
MB_LitrosNAVMB_LitrosD365 -
Adjust YTD Calculation: For the YTD calculation that includes December, you might need to explicitly define the accumulation of from January to November and then add for December. This might involve a more complex DAX formula that conditionally sums these measures based on the date range.
MB_LitrosNAVMB_LitrosD365 -
Example Revised Formula:
VAR CurrentYear = YEAR(MAX('DWH DimDate'[Date])) VAR StartOfYear = DATE(CurrentYear, 1, 1) VAR EndOfNovember = DATE(CurrentYear, 11, 30) VAR DecemberData = IF(MONTH(MAX('DWH DimDate'[Date])) = 12, MB_LitrosD365, 0) VAR YTDExcludingDecember = CALCULATE([MB_LitrosNAV], DATESBETWEEN('DWH DimDate'[Date], StartOfYear, EndOfNovember)) RETURN IF(MONTH(MAX('DWH DimDate'[Date])) = 12, YTDExcludingDecember + DecemberData, [MB_LitrosNAV])Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
-
1 Reply
Hi drivero ,
Here's a revised approach to your problem:
-
Refine Conditional Logic: Ensure that your conditional logic accurately reflects the transition between and . It's crucial that the condition checks not only the month but also aligns with your YTD calculation requirements.
MB_LitrosNAVMB_LitrosD365 -
Adjust YTD Calculation: For the YTD calculation that includes December, you might need to explicitly define the accumulation of from January to November and then add for December. This might involve a more complex DAX formula that conditionally sums these measures based on the date range.
MB_LitrosNAVMB_LitrosD365 -
Example Revised Formula:
VAR CurrentYear = YEAR(MAX('DWH DimDate'[Date])) VAR StartOfYear = DATE(CurrentYear, 1, 1) VAR EndOfNovember = DATE(CurrentYear, 11, 30) VAR DecemberData = IF(MONTH(MAX('DWH DimDate'[Date])) = 12, MB_LitrosD365, 0) VAR YTDExcludingDecember = CALCULATE([MB_LitrosNAV], DATESBETWEEN('DWH DimDate'[Date], StartOfYear, EndOfNovember)) RETURN IF(MONTH(MAX('DWH DimDate'[Date])) = 12, YTDExcludingDecember + DecemberData, [MB_LitrosNAV])Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
-