Calculate Month to Date Variance based on the Selected Month in Slicer

[waisal02]

Hi! I'm seeking help from the experts on dax coding to calculate Month-to-Date variance based on the selected month in a month slicer.

I have created a running total variance and difference chart based on the selected months, Max Month, and Min Month. The variance and difference work perfectly if both are past months or full months.

However, if the selected months include the current month, I would like the variance and difference to reflect the Month-to-Date values for both months. The desired result is illustrated in Image 2.

Current date = 10 Aug 2024

Image A: Both Selected Past Month

Image B: Selection Including Current Month

Below are my measure dax coding:

Max Month Count =

Calculate(sum('MOM'[Count]), filter(ALL('MOM'[Date].[Month]), 'MOM'[Date].[Month]=max('MOM'[Date].[Month])))

Min Month Count =

Calculate(sum('MOM'[Count]), filter(ALL('MOM'[Date].[Month]), 'MOM'[Date].[Month]=min('MOM'[Date].[Month])))

Diff = 'MOM'[Max Month Count]-'MOM'[Min Month Count]

Variance = DIVIDE('MOM'[Max Month Count]-'MOM'[Min Month Count], 'MOM'[Min Month Count])

[v-xianjtan-msft · Accepted solution]

Hi @waisal02 

May I ask if your problem has been solved? If not, I hope my solution was helpful to you.
1. I created the following measures in the test file you provided:

_Max Month Count =
VAR MaxDate = MAX(MOM[Date])
VAR MaxMonth = MONTH(MaxDate)
VAR MaxYear = YEAR(MaxDate)
VAR MaxMonthStart = DATE(MaxYear, MaxMonth, 1)
VAR MaxMonthEnd = IF(
MaxMonth = MONTH(TODAY()) && MaxYear = YEAR(TODAY()),
MAX(MOM[Date]),
EOMONTH(MaxMonthStart, 0)
)
RETURN
CALCULATE(
SUM(MOM[Count]),
FILTER(
MOM,
MOM[Date] >= MaxMonthStart &&
MOM[Date] <= MaxMonthEnd
)
)

_Min Month Count =
VAR MaxDate = MAX(MOM[Date])
VAR MinDate = MIN(MOM[Date])
VAR MinMonth = MONTH(MinDate)
VAR MinYear = YEAR(MinDate)
VAR MaxDayInMonth = DAY(MaxDate)
VAR MinMonthStart = DATE(MinYear, MinMonth, 1)
VAR MinMonthEnd = DATE(MinYear, MinMonth, MaxDayInMonth)
RETURN
CALCULATE(
SUM(MOM[Count]),
FILTER(
MOM,
MOM[Date] >= MinMonthStart &&
MOM[Date] <= MinMonthEnd
)
)

_Diff = MOM[_Max Month Count] - MOM[_Min Month Count]

_Variance = DIVIDE(MOM[_Max Month Count] - MOM[_Min Month Count], MOM[_Min Month Count])

2. Here is my final result:
The variance and difference work perfectly when both are full months.

However, if the selected months include the current month, need you to filter to the current date in the slicer, which ensures that the selected full month is based on the same date range as the current month calculation.

^{Best Regards,}

^{Jarvis Tang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Rakesh1705]

Preapared a Source Data for 01st Jan'24 to 31st Dec'24 with random values

Calculation of Month

Calculation of Day

Running total for current Month

Running total for Previous Month

Data Date Calculation

max date of current month

Previous Month Same Date

Previous month max date

Previous month Min date

Current month running total is modified with condition

Previous month running total is modified with previous months dates condition

Current month final total

Previous month same day final total

Variance is calculated

Present view is for current month and its previous month

View for Jun and Jul

If this method is serving your purpose then please accept the same as your solution

[tharunkumarRTK]

[Max Month Count] = 
Var __IsCurrentMonth = calculate( max('MOM'[Date].[Month]) allselected('mom')) = month(today()) Var result = if (__IsCurrentMonth, Calculate(sum('MOM'[Count]), filter(ALL('MOM'), 'MOM'[Date].[Month]=max('MOM'[Date].[Month]) && 'MOM'[DayNum] <= day(today()) )) , Calculate(sum('MOM'[Count]), filter(ALL('MOM'[Date].[Month]), 'MOM'[Date].[Month]=max('MOM'[Date].[Month]))) ) 
return result 

[Min Month Count] =
Var __IsCurrentMonth = calculate( max('MOM'[Date].[Month]) allselected('mom')) = month(today()) = month(today()) 
Var result = if (__IsCurrentMonth, Calculate(sum('MOM'[Count]), filter(ALL('MOM'), 'MOM'[Date].[Month]=min('MOM'[Date].[Month]) && 'MOM'[DayNum] <= day(today()) )) , Calculate(sum('MOM'[Count]), filter(ALL('MOM'[Date].[Month]), 'MOM'[Date].[Month]=min('MOM'[Date].[Month]))) )
return result 

you can modify the above two measures and it should solve your problem. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

@waisal02

[waisal02]

Hi! @tharunkumarRTK,
Thanks for your great help. Your dax coding provided should be work, for for some reason, i am bumped into some formating error.

I am hereby enclosed a test file and test data, hope you can assist.

MOM-TEST.pbix