Forum Discussion

Anonymous_'s avatar
Anonymous_
Frequent Visitor
2 years ago

QoQ calculation without Date Table

How do I calculate the QoQ change (for MT and %used) within the year (FY24Q2 vs FY24Q1) as well as over years (FY24Q1 vs FY23Q1, FY24Q1 vs FY23Q4)  without a date table? I also need to have the meas...
Need Help
Tutorial Requests
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am not sure about how your semantic model looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

 

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

MT measure: = 
SUM( Data[MT] )

 

Prev Qtr MT: = 
CALCULATE (
    [MT measure:],
    OFFSET (
        -1,
        ALL ( Data ),
        ORDERBY ( Data[Fiscal-Qtr], ASC ),
        ,
        ,
        MATCHBY ( Data[Fiscal-Qtr] )
    )
)

 

Prev Year MT: = 
CALCULATE (
    [MT measure:],
    OFFSET (
        -4,
        ALL ( Data ),
        ORDERBY ( Data[Fiscal-Qtr], ASC ),
        ,
        ,
        MATCHBY ( Data[Fiscal-Qtr] )
    )
)