Measure with time intelligence

[drivero]

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.

[Anonymous · Accepted solution]

Hi @drivero ,

Here's a revised approach to your problem:

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

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

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