Forum Discussion

kylee_anne's avatar
kylee_anne
Icon for Helper II rankHelper II
9 months ago
Solved

Parameters and DAX

Hi Everyone,   So is always the way it seems, I wrote some horrid clunky DAX as I was in a hurry.  This is an example.   CP_P6 Budgeted Units = CALCULATE(SUM('Resource B'[Value]),'Resource B'[Sp...
  • kylee_anne's avatar
    kylee_anne
    9 months ago

    Nope I put in parameter as a field not in a slicer, created a table using it with M and then could use it in DAX.  

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
9 months ago

kylee_anne , make sure you are having a date table, then you have MTD, this month last month etc using Time Intellignece 

CP_P6 Budgeted Units = CALCULATE(SUM('Resource B'[Value]),filter( 'Resource B', 'Resource B'[Spreadsheet Field]="Budgeted Units") )

Then have measure like 

MTD Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    DATESMTD('Date'[Date])
)

Last MTD Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    DATESMTD( DATEADD('Date'[Date], -1, MONTH) )
)

Last Month Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    PREVIOUSMONTH('Date'[Date])
)

Next Month Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    NEXTMONTH('Date'[Date])
)

This Month Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    DATESMTD( ENDOFMONTH('Date'[Date]) )
)

Last MTD (Complete) Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    DATESMTD( ENDOFMONTH( DATEADD('Date'[Date], -1, MONTH) ) )
)

Previous Month Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    PREVIOUSMONTH('Date'[Date])
)

Last Year Last Month Budgeted Units =
CALCULATE(
    [CP_P6 Budgeted Units],
    DATESMTD(
        DATEADD('Date'[Date], -1 * MONTH(TODAY()), MONTH)
    )
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.