Forum Discussion

lsfloyd8's avatar
lsfloyd8
New Member
2 years ago

Time Intelligence without Date Table

Hello! I need to calculate prior month and year over year variances. My data model (I have access to Report Builder, not the underlying data) has a field for month and a field for year. Both have the sigma sign next to them and I seem to be unable to use those fields as they are in quick measures.

How can I create the measures I need for prior month and year over year variances? 

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

    I use EOMONTH DAX function, and the description of the dax function's link is down below.

     

    EOMONTH function (DAX) - DAX | Microsoft Learn

     

     

    Prev month sales measure: = 
VAR _previousmonthenddate =
    EOMONTH ( MAX ( Sales[Date] ), -1 )
RETURN
    SUMX (
        FILTER (
            ADDCOLUMNS ( ALL ( Sales ), "@monthenddate", EOMONTH ( Sales[Date], 0 ) ),
            [@monthenddate] = _previousmonthenddate
        ),
        Sales[Sales]
    )