need help for dax

I have a table with columns: Date, Market, and Potential_dem_Cost. I need to create two measures: Next Month Potential_dem_Cost Next-to-Next Month Potential_dem_Cost These measures should dynam...

'Need help' 'DAX'

amitchandak
9 months ago
tkavitha911 , You can use TI various options are

MTD = CALCULATE(AverageX(values('Date'[Date]), calculate(SUM(Table[Qunatity Produced])) ),DATESMTD('Date'[Date]))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Trailing last month = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,MONTH))

next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

next t month = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],1,MONTH))

Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))

Power BI: Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/cyWVzAQF9YU?t=41169

MTD, Last Month, Trailing month
https://youtu.be/cyWVzAQF9YU?t=41662

wardy912

9 months ago

Hi tkavitha911

You need to use SELECTEDVALUE in your measures to dynamically work with the month selected in the filter

Next Month Potential_dem_Cost =
VAR SelectedMonth =
    SELECTEDVALUE('Date'[MonthNumber])
VAR SelectedYear =
    SELECTEDVALUE('Date'[Year])
VAR NextMonth =
    IF(SelectedMonth = 12, 1, SelectedMonth + 1)
VAR NextYear =
    IF(SelectedMonth = 12, SelectedYear + 1, SelectedYear)
RETURN
CALCULATE(
    SUM('YourTable'[Potential_dem_Cost]),
    FILTER(
        ALL('Date'),
        'Date'[MonthNumber] = NextMonth &&
        'Date'[Year] = NextYear
    )
)

Next-to-Next Month Potential_dem_Cost =
VAR SelectedMonth =
    SELECTEDVALUE('Date'[MonthNumber])
VAR SelectedYear =
    SELECTEDVALUE('Date'[Year])
VAR NextMonth =
    IF(SelectedMonth >= 11, SelectedMonth - 10, SelectedMonth + 2)
VAR NextYear =
    IF(SelectedMonth >= 11, SelectedYear + 1, SelectedYear)
RETURN
CALCULATE(
    SUM('YourTable'[Potential_dem_Cost]),
    FILTER(
        ALL('Date'),
        'Date'[MonthNumber] = NextMonth &&
        'Date'[Year] = NextYear
    )
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

Ahmed-Elfeel

Super User

9 months ago

Hi tkavitha911,

Here are two DAX measures that will calculate the Potential_dem_Cost for the next month and next-to-next month based on the currently filtered month:

First Measure:Next Month Potential_dem_Cost

Next Month Potential_dem_Cost = 
VAR CurrentMonthEnd = EOMONTH(MAX('Table'[Date]), 0)
VAR NextMonthStart = EOMONTH(CurrentMonthEnd, 0) + 1
VAR NextMonthEnd = EOMONTH(NextMonthStart, 0)
RETURN
CALCULATE(
    SUM('Table'[Potential_dem_Cost]),
    FILTER(
        ALL('Table'[Date]),
        'Table'[Date] >= NextMonthStart &&
        'Table'[Date] <= NextMonthEnd
    )
)

Second Measure:Next-to-Next Month Potential_dem_Cost

Next-to-Next Month Potential_dem_Cost = 
VAR CurrentMonthEnd = EOMONTH(MAX('Table'[Date]), 0)
VAR NextToNextMonthStart = EOMONTH(CurrentMonthEnd, 1) + 1
VAR NextToNextMonthEnd = EOMONTH(NextToNextMonthStart, 0)
RETURN
CALCULATE(
    SUM('Table'[Potential_dem_Cost]),
    FILTER(
        ALL('Table'[Date]),
        'Table'[Date] >= NextToNextMonthStart &&
        'Table'[Date] <= NextToNextMonthEnd
    )
)

Alternative Approach:Using DATEADD (if you have a proper date table)

// Next Month (with date table)
Next Month Potential_dem_Cost = 
CALCULATE(
    SUM('Table'[Potential_dem_Cost]),
    DATEADD('Date'[Date], 1, MONTH)
)

// Next-to-Next Month (with date table)
Next-to-Next Month Potential_dem_Cost = 
CALCULATE(
    SUM('Table'[Potential_dem_Cost]),
    DATEADD('Date'[Date], 2, MONTH)
)

Final Note:

For some cases when filtering December and January may show no data if it doesnt exist you might want to add error handling:

Next Month Potential_dem_Cost = 
VAR CurrentMonthEnd = EOMONTH(MAX('Table'[Date]), 0)
VAR NextMonthStart = EOMONTH(CurrentMonthEnd, 0) + 1
VAR NextMonthEnd = EOMONTH(NextMonthStart, 0)
VAR Result = 
    CALCULATE(
        SUM('Table'[Potential_dem_Cost]),
        FILTER(
            ALL('Table'[Date]),
            'Table'[Date] >= NextMonthStart &&
            'Table'[Date] <= NextMonthEnd
        )
    )
RETURN
IF(ISBLANK(Result), 0, Result)  // Returns 0 instead of blank

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Forum Discussion

need help for dax