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.
