Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
Please help me with an alternative DAX command as I'm in Direct Query Mode which does not support DATESBETWEEN function.
Two Weeks Ago = var todays_date = MAX('Supplier OTD'[Posting Date in the Document]) var two_weeks_ago_date = todays_date - 14 var otd_percent = IF(CALCULATE ( COUNTROWS('Supplier OTD'),'Supplier OTD'[OTD]="On Time")<>0 , CALCULATE ( COUNTROWS('Supplier OTD'),'Supplier OTD'[OTD]="On Time") / COUNT('Supplier OTD'[OTD]),0.05) return CALCULATE(otd_percent, DATESBETWEEN('Supplier OTD'[Posting Date in the Document],two_weeks_ago_date,todays_date) )
I'm trying to find OTD for the various time range. For example Two weeks ago, One month ago, One quarter ago as header and their OTD as rows values, but I'm getting the same value in all the three columns.
Thanks in advance
Solved! Go to Solution.
Hi @viveksasi94
Assume your data is like
Create measures in the table (for example, two weeks ago)
today = CALCULATE ( MAX ( [Posting Date in the Document] ), ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ) ) two weeks ago measure = [today]-14 otd_count = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [OTD] = "on time" && [Posting Date in the Document] >= [two weeks ago] && [Posting Date in the Document] <= [today] ) ) otd_total = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [Posting Date in the Document] >= [two weeks ago] && [Posting Date in the Document] <= [today] ) ) percent = [otd_count]/[otd_total]
Or you could nest these measures into one measure
Two Weeks ago = VAR today = CALCULATE ( MAX ( [Posting Date in the Document] ), ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ) ) VAR twoweeksago = [today] - 14 VAR otd_count1 = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [OTD] = "on time" && [Posting Date in the Document] >= [two weeks ago measure] && [Posting Date in the Document] <= [today] ) ) VAR otd_total1 = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [Posting Date in the Document] >= [two weeks ago measure] && [Posting Date in the Document] <= [today] ) ) RETURN [otd_count] / [otd_total]
Best Regards
Maggie
Hi @viveksasi94
Assume your data is like
Create measures in the table (for example, two weeks ago)
today = CALCULATE ( MAX ( [Posting Date in the Document] ), ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ) ) two weeks ago measure = [today]-14 otd_count = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [OTD] = "on time" && [Posting Date in the Document] >= [two weeks ago] && [Posting Date in the Document] <= [today] ) ) otd_total = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [Posting Date in the Document] >= [two weeks ago] && [Posting Date in the Document] <= [today] ) ) percent = [otd_count]/[otd_total]
Or you could nest these measures into one measure
Two Weeks ago = VAR today = CALCULATE ( MAX ( [Posting Date in the Document] ), ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ) ) VAR twoweeksago = [today] - 14 VAR otd_count1 = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [OTD] = "on time" && [Posting Date in the Document] >= [two weeks ago measure] && [Posting Date in the Document] <= [today] ) ) VAR otd_total1 = CALCULATE ( COUNT ( 'Supplier OTD'[OTD] ), FILTER ( ALLEXCEPT ( 'Supplier OTD', 'Supplier OTD'[name] ), [Posting Date in the Document] >= [two weeks ago measure] && [Posting Date in the Document] <= [today] ) ) RETURN [otd_count] / [otd_total]
Best Regards
Maggie
does the && argument work
CALCULATE(otd_percent,
'Supplier OTD'[Posting Date in the Document] >= two_weeks_ago_date
&& 'Supplier OTD'[Posting Date in the Document] <= todays_date)
)
Thanks for your contribution, but still I'm getting the same row values for all the three different time range. This is how I want it to
look like.
But now I'm getting the same percentage value for all the time periods for the above DAX command.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |