topic Re: If a Date is selected I need to get the Last 14 days data. in DAX Commands and Tips

If a Date is selected I need to get the Last 14 days data.

Pker_Tank — Sun, 29 Dec 2024 11:08:20 GMT

Hi,
I am stuck with this use case, and need your assistance.
If a user selects a date ending from the date filter (ex: 14 Dec) he should see the data from 1st Dec to 14th Dec.
I have a date table mapped to Actual tables date column.
So the filter will be using the date table, Date column.
In the table view, I will be using Date column of my actual table.
Hope it makes sense. Pleaselet me know if more information needed. Thanks heaps guys

Re: If a Date is selected I need to get the Last 14 days data.

Jihwan_Kim — Sun, 29 Dec 2024 14:36:14 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.

last 14 days sales: = VAR _dateselect = MAX ( slicer_calendar[date] ) RETURN CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( 'calendar'[date] <= _dateselect ), KEEPFILTERS ( 'calendar'[date] >= _dateselect - 13 ) )

Re: If a Date is selected I need to get the Last 14 days data.

Pker_Tank — Sun, 29 Dec 2024 21:43:47 GMT

Hi, Sorry I was not clear before.

I have a table with columns like ID, NAME, TRANSDATE, CLASS.
I have a Date Table with one Date Column pulling all the dates I needed.
I Mapped the Date Table to My original table with Data column 1 to many mapping.

Now, In my slicer, I am using Date tables, Date column. When I select any date in the slicer, It should automatically filter the last 14 days of dates in my original table

For example: This table with dates should appear if I select 14th Dec in my slicer.

ID	Name	Transdate	Class
3	sfv	1/12/2024	a
5	bre	2/12/2024	a
7	rgw	3/12/2024	a
9	njyrt	4/12/2024	a
43	shte	5/12/2024	a
545	trh	6/12/2024	a
757	erht	7/12/2024	a
34	thr	8/12/2024	a
4564	terh	9/12/2024	a
76	thr	10/12/2024	a
43	thr	11/12/2024	a
24	rhydt	12/12/2024	a
57	hry	13/12/2024	a
54657	dfbt	14/12/2024	a

Re: If a Date is selected I need to get the Last 14 days data.

Jihwan_Kim — Mon, 30 Dec 2024 03:43:34 GMT

Hi,

In my opinion, it still needs disconnected date table and this needs to be used in the slicer visualization.

Thank you for sharing how the expected result looks like, and if it is OK, please share the sample data that you are using behind the result.

It will be helpful if you can share the sample pbix file's link.

Thank you.

Re: If a Date is selected I need to get the Last 14 days data.

Anonymous — Tue, 31 Dec 2024 06:50:11 GMT

Hi Pker_Tank

Thanks for the reply from Jihwan_Kim .

Pker_Tank, please refer to the following test.

Sample:

No relationship between two tables

Create a measure as follows

Measure = VAR _selected = SELECTEDVALUE('Date'[Transdate]) RETURN IF(_selected = BLANK(), 1, IF(MAX([Transdate]) >= _selected - 13 && MAX([Transdate]) <= _selected, 1, 0))

Put the measure into the visual-level filters, set up show items when the value is 1.

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: If a Date is selected I need to get the Last 14 days data.

Poojara_D12 — Wed, 01 Jan 2025 07:24:48 GMT

Hi Pker_Tank

Create a Measure to filter dates from the 1st of the month to the selected date:

ShowDates = VAR SelectedDate = MAX('DateTable'[Date]) VAR StartOfMonth = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1) RETURN IF( MAX('ActualTable'[Date]) >= StartOfMonth && MAX('ActualTable'[Date]) <= SelectedDate, 1, 0 )

Apply the Measure: Add ShowDates as a visual-level filter and set it to 1.

Use a Date Slicer: Add a slicer with the DateTable[Date]. Selecting a date (e.g., 14 Dec) will show rows from 1st Dec to 14th Dec.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: If a Date is selected I need to get the Last 14 days data.

johnt75 — Thu, 02 Jan 2025 16:28:29 GMT

Have a look at https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/