cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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])
1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Resolver III

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

Super User
``````[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

 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

Regular Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors