Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Year wise Month on Month comparison Dax

I have the data  from 2018 to 2025 . this data looks like  . i want comparision based on difference selection. if i select 1  Range 2019 January  and 2018 Januray value comparision . below formula used in Excel 

=(R5-F5)/F5

 

so created below formula for the measure 

 

n =
SELECTCOLUMNS(
    GENERATESERIES(1,12),
    "n", [Value]
)
 
Calculated column 
 
MonthSN = (Base[Year value].[Year]*12+[Month - Number])

 

 

MoM Difference =
VAR _currentmonthSN = MAX(Base[MonthSN])
VAR _n = SELECTEDVALUE(n[n])
VAR rate =
    DIVIDE(
        SUM(Base[Value]),
        CALCULATE(
            SUM(Base[Value]),
            ALL(Base),
            Base[MonthSN]=_currentmonthSN-_n
        )
    )^(1/_n)
    -1
RETURN
    IF(rate<>-1, rate, "")

 

 

Base Table :

 

Date                             Value                          Region

01-01-2018                  23                               EAST

01-01-2018                  43                               WEST

01-02-2018                  23                               EAST

01-02-2018                  43                               WEST

01-03-2018                  23                               EAST

01-03-2018                  43                               WEST

----

------

01-01-2019                 36                               EAST

01-01-2019                 62                               WEST

01-02-2019                 23                               EAST

01-02-2019                 53                               WEST

01-03-2019                 83                               EAST

01-03-2019                 93                               WEST

 

 

Looking for support .. thanks in advance ..

0 REPLIES 0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.