Time intelligence Functions

[marsclone]

I’m trying to build a matrix with different columns with month (actual/budget) and year (actual/budget/last year) values.

With a ‘month’ slicer i can filter the data.

Some of the formulas are working, some not.

Working – Monthvalue (October) Actual:

IST Amount MTD = TOTALMTD([IST];Datum[Date])

Working – Monthvalue (October) Budget:

PLAN Amount MTD = TOTALMTD([PLAN];Datum[Date])

Not Working – Yearvalue (January-October) Lastyear:

IST Amount LYTD = TOTALYTD([IST];PREVIOUSYEAR(Datum[Date]))

Working - Yearvalue (January-October) Actual:

IST Amount YTD = TOTALYTD([IST];Datum[Date])

Working - Yearvalue (January-October) Budget:

PLAN Amount YTD = TOTALYTD([PLAN];Datum[Date])

Not Working – Yearvalue (January-December) Budget:

PLAN Amount Year = CALCULATE([PLAN])

Working – Yearvalue (January-December) Lastyear:

IST Amount LY = CALCULATE([IST Amount YTD];PREVIOUSYEAR(Datum[Date]))

I’ve added a testfile, the first sheet is showing the right values only per matrix. The second sheet shows the values in one matrix.

Test File

Thank for your help.

[Anonymous]

Hi @marsclone,

Time intelligence functions seems useful to calculate partial data period calculation, but it is not suitable for nested calculation and calculate with specific filters.

I'd like to suggest you take a look at following blog about time intelligence calculation and how to use date function to manually filter date range to calculate:

Secret of Time Intelligence Functions in Power BI

Time Intelligence "The Hard Way" (TITHW)

Regards,

Xiaoxin Sheng