DAX show data from previous period

[Anonymous]

Hello guys,

I've been working with differents measures and I am still stuck.... That's why I wanna ask you guys.

I'm currently working in Slicer where I already have pre-defined the dates (ex: 16/03/2020, 23/03/2020, etc. (7 days difference)) and my idea is to show data from the previous date. The main issues come from when I need to include DAX in my measures. An example of one measure:

Mails =

CALCULATE(

SUM(XXX[Value]);Filter ('XXX';'XXX'[Indicators] IN {"Managed mails"; "Deleted Mails"}))

My idea is including the previous measure with some DAX which shows me the date from the previous period and not the one which I selected on the Slicer.

Any idea of how to carry out with this? 

Many thanks,

[kentyler]

One approach would be to use a Date or Calendar table and have one the columns be the "offset" date, so you would not have to calculate it, you could just use it as an attribute of the selected date.
so the "offset" date for  16/03/2020 would be 23/03/2020, 
then in any DAX measure where you might use the selected date, you can just substitute using the offset date

[Anonymous]

The issue comes when I have to include that "approach" into my measures. That's why I set an example with one of my created measures. And I truly prefer to include the solution in a Card insted of a table. Any idea?

Thanks,

[kentyler]

I do not see where your sample measure makes any reference to the selected date from the slicer. Does the slicer filter the data you are using in your calcuate() ?  If so you would set up the slicer to display the date the user wants to select, but to filter by the "offset" date.

I don't understand your questions about using a card to display results... if the measure returns a count you can display it on a card.

[Anonymous]

Sorry, I am quite new in DAX so I may not be clear in my question, I am gonna try to explain it in a understandable way: 

1- As shown in the following date, that's my excel, so my idea is to sum all the numbers of a certaing indicator and date. So one if a want to see X1 indicator, show me the total number (sum 1+1+1 = 3). With dates row I changed it to unpivot table to make an slicer (so if I choose one date in the slicer it filters the measures created). 

	Week
Indicator	16-03-20	23-03-20	30-03-20	06-04-20
X1	1	1	1
X2		2		5
X3	1

2-  I created many measures to carry out my idea:  

Mails =

CALCULATE(

SUM(XXX[Value]);Filter ('XXX';'XXX'[Indicators] IN {"X1"; "X2"}))

That's works very well, there's no issues. So my next step is to show my previous data, so if i select on the slicer 23-03-2020, the measure (or whatever) has to show me the previous data.

I don't know if I am clear with my issue. If it's so, there's any way to do it with cards? Or maybe using tables?

Many thanks for you time and effort!

[Ashish_Mathur]

Hi,

You should have a Calendar Table with a relationship from the Attribute column of XXX table to the Date column of the Calendar Table.  Your slicer should be build from the Date column of the Calendar Table.  Write this measure

=CALCULATE([Mails],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-7,MIN(Calendar[Date])-7))

Hope this helps.

[Anonymous]

// It's actually very easy to do what you want
// but only on condition your model is built
// correctly. Calendar table must be THE
// date table in the model connected to your
// fact table. The days that belong to one
// week should have a column next to them with the date
// of the first day of the week. This should be
// named something like [Week Start]. These week starts
// should be in your slicer as well.

[Mails PW] = // mails previous week
var __oneWeekVisible = HASONEVALUE( 'Calendar'[Week Start] )
var __dayLag = 7 // back one week
var __result =
	CALCULATE(
		[Mails],
		DATEADD(
			'Calendar'[Date],
			-__dayLag,
			DAY
		)
	)
return
	if( __oneWeekVisible, __result )

Best

D