Using DATEADD where there are multiple date rows of data

DAX DAX Calculations
1

Hi all, I am new to using the forum - I hope this is the correct format and place for this question.

I have data similar to this (this is simplified for the example):

Date State A B
02/08/2020 VIC 2 4
02/08/2020 NSW 3 5
02/08/2020 WA 3 6
03/08/2020 VIC 6 3
03/08/2020 NSW 1 1
03/08/2020 WA 4 3
04/08/2020 VIC 3 6
04/08/2020 NSW 7 7
04/08/2020 WA 2 4

I would like to be able to filter the data by individual State or All for reporting and to have measures for the previous day to produce tables (and later graphics) similar to those below:

Filter: ALL

Date A B Previous day A Previous day B
02/08/2020 8 15
03/08/2020 11 7 8 15
04/08/2020 12 17 11 7

Filter: VIC

Date A B Previous day A Previous day B
02/08/2020 2 4
03/08/2020 6 3 2 4
04/08/2020 3 6 6 3

Currently everything that I have tried produces nothing for the Previous A and Previous B columns.

Can this be done? Your help would be greatly welcomed.

Thanks, Simon.

1 Like
2

Hello @simonbb,

Thank You for posting your query onto the Forum.

I’m providing the screenshot as well as the Excel and PBIX file of my working below for the reference -

DATEADD
DATEADD921×476 23.1 KB

Hoping you find this useful and meets your requirements that you’re looking for. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

Exp.xlsx (8.3 KB)

DATEADD.pbix (70.5 KB)

1 Like
3

Hi @simonbb,

Welcome to the Forum!

The data in column A and B seems similar so I used Unpivot other columns to change the format
image

Also added a Date table and Marked that as date table
Created a Relationship between the two tables
image

Next I created a simple measure

Value = 
SUM( 'Fact Table'[Value] )

Instead of SUM you can use any aggregate function.

Created a second measure

Prev value = 
CALCULATE( [Value], PREVIOUSDAY( Dates[Date] ))

with this result.
image

If you place filters for example via slicers you can change what dimensions are visible within the table.

I would suggest going over these modules.

Here’s my sample file. eDNA - multiple date rows.pbix (63.3 KB)

I hope this is helpful.

2 Likes
4

Hi @simonbb, I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

Hi Melissa,

Thanks you for your welcome and your proposed solution. It did not quite address my problem but it did lead me to finding the solution that I required. What you solution has done has identified ares of DAX that I need to work on.

The issue I had that caused my measure not to work was I had:

Prev value =
CALCULATE( [Value], PREVIOUSDAY( Dates[Date].[Date]))

Once I removed the .[Date] if addressed my problem. I was close :slight_smile:

Regards,
Simon

6

Thanks for your asssistance Harsh.

Regards,
Simon

7

Glad to hear it’s resolved :+1:

You get that .[Date] because of the enabled auto date time feature. I can’t emphasize enough to always include a proper Date dimension table when you have a date field anywhere in your model and mark it as such. And to disable the auto date time feature globally in the Power BI settings.

All the best