Parameters and DAX

[kylee_anne]

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.

[Punithurs]

Hi, 
refer thia video. 
https://www.youtube.com/watch?v=90h9h8gN_LE 

Thanks,
Punith

[kylee_anne · Accepted solution]

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]

@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-bd52912a5b...
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.

[kylee_anne]

Is it not possible to just use parameters?

[amitchandak]

@kylee_anne , Parameter means slicer here. If you are calling URL you can pass the parameters. You can reduce measures using Calculation groups 

Service URL, when you call a report-https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters

You need to have slicers or filters. 

We have a dynamic M parameter for direct query- https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

We can have a calculation group to rescue measures- Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0