Time Intelligence without Date Table

lsfloyd8 — Mon, 13 Nov 2023 16:50:03 GMT

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?

Re: Time Intelligence without Date Table

Jihwan_Kim — Mon, 13 Nov 2023 17:51:32 GMT

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

topic Re: Time Intelligence without Date Table in DAX Commands and Tips

Time Intelligence without Date Table

Re: Time Intelligence without Date Table