topic Calculate Month to Date Variance based on the Selected Month in Slicer in DAX Commands and Tips

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

Anonymous — Sat, 10 Aug 2024 09:54:26 GMT

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])

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

tharunkumarRTK — Sat, 10 Aug 2024 13:23:07 GMT

[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!

Anonymous

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

Anonymous — Sun, 11 Aug 2024 06:13:06 GMT

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

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

Anonymous — Sun, 11 Aug 2024 09:55:57 GMT

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

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

Anonymous — Wed, 14 Aug 2024 03:59:27 GMT

Hi Anonymous

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.}