Forum Discussion

kylee_anne's avatar
kylee_anne
Helper II
8 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'[Spreadsheet Field]="Budgeted Units",'Resource B'[Date]<=DATE(2025,10,30))+0
 
Rather than hard coding the dates for month end and the prior month end which are cumulatively used in about 20 DAX statements and 1 table construction query can I use parameters here?  I'm trying options but I can't seem to pick parameters up in DAX.
 
What am I missing or is there a better way?
 
Thanks.

 

  • kylee_anne's avatar
    kylee_anne
    8 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.  

5 Replies

  • kylee_anne's avatar
    kylee_anne
    Helper II
    8 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
    Super User
    8 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.