Hi ,Im facing an issue taking MTD ,Last MTD and Last year MTD values for a calculated field which calculates UPT(unit per transaction) which is quantity*1000/invoices. This is a row context field. I created a calculated field which has an ifelse which takes if the dates are relevant in MTD then it will take Upt else 0.But when i put this field in KPI and take average this is taking the whole number of dates. If today is May 10 th i want 10 day sum of UPT s /10 instead it is taking denominator as total no of days in the Data…Is there any function which does the same work as FILTER in DAX
Hello @vinay-vikram, it seems like you would want to also add logic to make sure business date is not only less than or equal to your custom date, but greater than the first of the month. If you use the extract calculation with addDateTime in QuickSight, we should be able to add that in. I’ll link documentation and provide an example below:
ifelse(
dateDiff({business_date}, ${Customdate}, "MM") = 0 AND {business_date} <= ${Customdate}
AND {business_date} >= addDateTime(- (extract('DD', ${Customdate}) - 1), 'DD', ${Customdate}), UPT,
0
)
I will mark this as the solution, but please let me know if you have any remaining questions.
Thank you so much…I will check this out
Hi,I think the problem is when i call the UPT calculation here what this calculated field is doing is put the UPT values for significant dates and others 0 when I use ifelse…So when I take average of this still the rows(the other dates)where UPT is 0 is also considered. What is want is according to the dates I am choosing the number of days or denominator should change…
`ifelse(dateDiff({business_date},${Customdate},“MM”) = 0 AND {business_date}<=${Customdate} ,
sum({UPT})/extract(‘DD’,${Customdate}),NULL)``
I got this idea of taking sum of UPT dates in this month and my denominator is number of days which is the day part of the date. But its showing mismatched aggregation. Is it because extract returns it in another datatype?
Hey Dylan,It was one blunder by me which I didnt notice, by replacing 0 by NULL in Ifelse it worked because average wont count null . But Thanks for responding so fast . Have a Great Day
1 Like
Hello @vinay-vikram, thank you for following it up! I am glad you were able to resolve the issue.