Dax Finding date based on Criteria calculated Column

0
votes

I couldn't find an answer for my issue elsewhere, so trying my luck here.

I have sales table and my final result should determine if there were sales made for same person in specific period of time, for example within 7 business days. for example: sales table

For ID 123 I have to flag it that sale for products A,B,C where within specified period.

For ID 1234 only sales of products A and B meet the criteria product C was sold in irrelevant time frame.

I've created a date table with indicators that determine for each date if the date is a working day, but i am struggling to calculate the relevant last working day Date Table

For example: I need that for 01/01/2019 i will get 10/01/2019 date, based on NUMOFDAYS and FinalWorkday = TRUE, which basically means that i have to count NUMOFDAYS times TRUE statement for each date and return corresponding date.

After that step done I think that it would be pretty easy for me to determine if the sale for a person was made in specific time frame.

If someone can give me direction for that much appreciated

Thank you in advance

2
powerbidaxpowerpivot

2 Answers

0
votes

You could use a DateTable like this one:

enter image description here

I used the following DAX-expressions for the calculated columns:

nrDays = 7

isWorkDay = WEEKDAY('DateTable'[Date],2) < 6

rankWorkingDays =  RANKX ( FILTER ( DateTable, DateTable[isWorkDay] = TRUE () ),
    DateTable[Date] , , ASC )

LastWorkDay = LOOKUPVALUE ( DateTable[Date],
    DateTable[isWorkDay], TRUE (),
    DateTable[rankWorkingDays], DateTable[rankWorkingDays] + DateTable[nrDays])
0
votes

This issue can be solved by the following, since three are non-working days/holidays we can filter them out via POWERQUERY, than add an Index Column and Another column Which is basically Index column + Number of days wanted, then simply merge duplicate of dates query on Index+number of days wanted column on Index column. And we get the correct date