Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |