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!
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() ) )