need help for dax

[tkavitha911]

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 dynamically calculate the Potential_dem_Cost for the next month and the month after that, based on the currently selected month in the filter. For example, if I filter January, the measure should return:

• Next Month = February’s Potential_dem_Cost
• Next-to-Next Month = March’s Potential_dem_Cost.

[amitchandak · Accepted solution]

@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

[Ahmed-Elfeel]

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.

[v-prasare]

Hi @tkavitha911,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @tkavitha911 ,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Praful_Potphode, @wardy912 & @amitchandak  ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Praful_Potphode]

Hi @tkavitha911 ,

try below:

Next Month Potential_dem_Cost = 
CALCULATE(
    SUM('Potential_dem_Cost'[Cost]),
    DATEADD('Date'[Date], 1, MONTH)
)

Next-to-Next Month Potential_dem_Cost = 
CALCULATE(
    SUM('Potential_dem_Cost'[Cost]),
    DATEADD('Date'[Date], 2, MONTH)
)

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[wardy912 · Accepted solution]

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!

[amitchandak · Accepted solution]

@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