Forum Discussion
Avoid Slicer not filter
Hi Experts,
I am trying to get a "count of Tasks" for Previous Period (PP). So for example, if the date filter is from 2020-03-01 to 2020-04-02, then difference between from and to dates = 32 days, so the previous period would be calculated as
From Date filter for PP : (From date=2020-03-01) - 1 - 32 days = 2020-01-28
To Date filter for PP : (From date=2020-03-01) - 1 day = 2020-02-29
This measure is sliced by Location attribute to view number of tasks in PP by location
Relation in Model : DimDate <---> FactTask <----> DimLocation
Fact Data:
FactKey TaskID LocationID DateKey
1 1 1 20200302
2 2 2 20200227
3 3 1 20200125
4 4 2 20200220
5 5 1 20200302
As a 1st step, I used FIRSTDATE() function to get "from date" as "2020-03-01" but rather i get firstdate for that location,
So for Location ID = 1 i get FirstDate as "2020-01-25" and for Location ID = 2 i get FirstDate as "2020-02-20" which isn't what i want. This leads to incorrect values. The issue is if i use FirstDate/Min function to get the "From Date" from date filter its getting sliced by location and i am getting wrong date.
I am using below dax formula:
=CALCULATE([Count of TaskID],DATESBETWEEN(DimDate[Date], DATEADD(PREVIOUSDAY(FIRSTDATE(DimDate[Date])),-1*(DATEDIFF(FIRSTDATE( DimDate[Date] ), LASTDATE(DimDate[Date] ), DAY )),DAY),PREVIOUSDAY(FIRSTDATE(DimDate[Date]))))
Thanks,
Amar
This can be marked as resolved.
I tried by changing the Filter direction from DimDate to Fact from "Both ways" to only "one way" and it worked. I also recently found this link: https://community.powerbi.com/t5/Desktop/Show-items-with-no-data-doesn-t-work-for-dates/td-p/863715
where the same thing was done.
This works for that issue but I had to drop the relation between DimDate to Fact to prevent date from filtering attributes but it should filter measures.,
Thank you,
Amar
4 Replies
amarsale85 I don't know how you are doing it, assuming you are using date table where you selected the range and this is what I will do for PP
PP = VAR __minDate = MIN ( DateTable[Date] ) VAR __maxDate = MAX ( DateTable[Date] ) VAR __numberofDays = DATEDIFF ( __minDate, __maxDate, DAY ) VAR __ppStartDate = __minDate - _numberofDays - 1 VAR __ppEndDate = __minDate - 1 RETURN CALCULATE ( SUM ( DataTable[Column] ), DATESTBETWEEN ( DateTable[Date], __ppStartDate, __ppEndDate ) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Thank you for the Answer parry2k but i am 100% sure it cannot resolved in Power BI using dax measure as it was a model design issue which resolved this issue thankfully after wasting 10 hours of my time.
Hi , amarsale85
Could you please tell me whether your problem has been solved?
If it is ,please mark the helpful replies or add your reply as Answered to close this thread.
Best Regards,
Community Support Team _ Eason
