topic Re: Use of Previous Date in DAX Commands and Tips

Use of Previous Date

rafaelcostatl — Mon, 08 Jul 2019 12:38:22 GMT

Greetings,

I found a DAX that gives me the previous date of a given base date and it works

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/717ee0bf-5fbb-4731-a198-7a27f8937bab/dax-measure-to-find-date-of-previous-record-for-each-piece-of-data-in-a-time-series-help?forum=sqlkjpowerpivotforexcel

This is already an improvemente to my solution, which basically loads again a table with "date - 1 " as previous date to get the comparison going. Improvement because now the previous date ajusts to "wholes" (months when I have null values of a give measure). My decrementing solution would always look at the previous date, no matter if zero or null or whatever.

Now, what I want is a dax that calculates the previous measure for me, letting me go from my original solution completely, with a more beautifull and optimized solution:

I tried:

//CODE START

PREV_CALC_MEASURE=

CALCULATE((SUMX('TABLE';'TABLE'[VALUE]));

FILTER(ALL('TABLE';'TABLE'[VALUE]);'TABLE';'TABLE'[VALUE])=

//THE LINE BELLOW IS THE DAX THAT GIVES ME THE PREVIOUS DATE

CALCULATE(MAX(Fact[Date]),FILTER(Fact,Fact[Date]<EARLIER(Fact[Date])))

))

// END OF CODE

My solution with the double load of a table with the dax previous code already works as I need. I just want this code to get more classy :D

Similar to which we can do in qlik "set analysis", I am trying to calculate with a date restriction. The code that gives me the previous date works, so it is some mistake of mine that is preventing me from getting the desired result.

Anyway, I hope this helps other people too.

Thank you already,

Best Regards

Rafael

Re: Use of Previous Date

Stachu — Fri, 12 Jul 2019 08:09:37 GMT

Hi, can you add sample tables from your model with anonymised data?

As described here:

Re: Use of Previous Date

rafaelcostatl — Fri, 12 Jul 2019 15:06:11 GMT

Hello,

Thank you for your response and guidance.

In the screenshot, we can see that the dax funcion gives me the previous date. I want to do that calculation directly in the left table, so I can calculate the value for the previous date of a given one. Right now, my solution uses the right table. If what I want is possible, I should be able to creat another colum in the left table with the value of the previous date.

Thank you again.

Re: Use of Previous Date

Stachu — Fri, 12 Jul 2019 15:28:18 GMT

can you show the source tables, preferably in the format that can be copied into PowerBI, rather than output tables?

also I don't really get the date format used, e.g.

20140101.01

20140101.05

is it 1st Jan 2014? What's the meaning of .01/.05 after the date itself? Does the column have the date type or just a number type?

Re: Use of Previous Date

rafaelcostatl — Fri, 12 Jul 2019 16:34:35 GMT

Hello,

I thought the name would make it self explanatory. Sorry. It is date_code because 201401 is january of 2014. The code attached to it is a random code (20140101.01). The idea remains the same (the code does not change anything). I put it all this way to make it quickly. I will extract the tables and post it here then :)

<https://drive.google.com/drive/folders/1IXR1rnwX4XEGx_wXOrWYXC2zCEhdOdTJ?usp=sharing>

Thx again