Power Platform Community Forum Thread Details

Hi all,

I am trying to filter the table by date value to show only last 4 days of data and trying to compose into a HTML table but it does not filter or retrun the data properly.

For example, todays date is 28/05/2024 and if I run the query to filter and return last 4 days, expected output to return data for 25,26,27 and 28 (include today). But it return the data only for 28th May 2024.

Here is the DAX query that is used to run against the dataset, VAR __DS0FilterTable2 :

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"White/Yellow"}, 'PotatoPackoutMainlines'[Variety])

	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('Calendar'[DateValue])),
			AND(
				'Calendar'[DateValue] >= TODAY() - 4,
				'Calendar'[DateValue] = TODAY()
			)
		)

	VAR __ValueFilterDM3 = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'Calendar'[DateValue],
					'PotatoPackoutMainlines'[ProductionArea],
					'PotatoPackoutMainlines'[AM/PM],
					__DS0FilterTable,
					__DS0FilterTable2,
					"SumTonnesDelivered", CALCULATE(SUM('PotatoPackoutMainlines'[TonnesDelivered])),
					"SumTonnesTipped", CALCULATE(SUM('PotatoPackoutMainlines'[TonnesTipped])),
					"SumTonnesPacked", CALCULATE(SUM('PotatoPackoutMainlines'[TonnesPacked])),
					"v__Total_Waste2", 'PotatoPackoutMainlines'[# Total Waste],
					"SumPacked_Premium", CALCULATE(SUM('PotatoPackoutMainlines'[Packed_Premium])),
					"v__Total_Waste", 'PotatoPackoutMainlines'[% Total Waste],
					"v__Premium_Packed", 'PotatoPackoutMainlines'[% Premium Packed],
					"v__Diff__PD_v2", IGNORE('PotatoPackoutMainlines'[# Diff. PD v2])
				)
			),
			NOT(ISBLANK([v__Diff__PD_v2]))
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(
				ROLLUPGROUP(
					'Calendar'[DateValue],
					'PotatoPackoutMainlines'[ProductionArea],
					'PotatoPackoutMainlines'[AM/PM]
				), "IsGrandTotalRowTotal"
			),
			__DS0FilterTable,
			__DS0FilterTable2,
			__ValueFilterDM3,
			"SumTonnesDelivered", CALCULATE(SUM('PotatoPackoutMainlines'[TonnesDelivered])),
			"SumTonnesTipped", CALCULATE(SUM('PotatoPackoutMainlines'[TonnesTipped])),
			"SumTonnesPacked", FORMAT(CALCULATE(SUM('PotatoPackoutMainlines'[TonnesPacked])),"#0"),
			"v__Total_Waste2", FORMAT('PotatoPackoutMainlines'[# Total Waste],"#0"),
			"SumPacked_Premium", FORMAT(CALCULATE(SUM('PotatoPackoutMainlines'[Packed_Premium])),"#0"),
			"v__Total_Waste", FORMAT('PotatoPackoutMainlines'[% Total Waste],"#0.00%"),
			"v__Premium_Packed", FORMAT('PotatoPackoutMainlines'[% Premium Packed],"#0.00%")
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			501,
			__DS0Core,
			[IsGrandTotalRowTotal],
			1,
			'Calendar'[DateValue],
			1,
			'PotatoPackoutMainlines'[ProductionArea],
			1,
			'PotatoPackoutMainlines'[AM/PM],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal],
	FORMAT('Calendar'[DateValue],"yyyy-MM-dd"),
	'PotatoPackoutMainlines'[ProductionArea],
	'PotatoPackoutMainlines'[AM/PM]

Attached image is the output image.

How should I resolve this ? Is there any other way of writing the date filter query?

Thanks for the help in advance!