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