Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Date filter against a dataset in Power BI is not filtering Properly

(0) ShareShare
ReportReport
Posted on by 9

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!

  • ricpower00 Profile Picture
    ricpower00 42 on at
    Re: Date filter against a dataset in Power BI is not filtering Properly

    It looks like your date logics are contradicting, and you need to remove the second condition

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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard