Time Intelligence without Date Table

[lsfloyd8]

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? 

[Jihwan_Kim]

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