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, I'm quiete new on Power BI desktop and I face the following issue: I have a "DimDate" (generated by calendar auto DAX fx) table that starts on 01/01/2017 and finishes 31/12/2018. My target is to count how many weeks we have in year to date. As we are the 08/01/2018, the expected result is 1 or 2.
The table is handle like this
- "date" column
- "week_number" column (generetad with weeknum dax fx)
I used the following formulas to get the number of weeks in year to date:
- Number_of_weeks = distinctcount (DimDate[week_number])
- Number_of_weeks_YTD = calculate ([Number_of_weeks]); datesytd(DimDate[Date]))
Result is 56. I really don't understand why it counts the whole weeks number on 2018 instead the YTD... Can someone has a clue and can help me ? Thanks a lot.
Nicolas.
Solved! Go to Solution.
Hi @Anonymous,
If I understand you correctly, the formula below should work in your scenario.
Number_of_weeks_YTD = VAR currentDate = TODAY () RETURN CALCULATE ( DISTINCTCOUNT ( DimDate[week_number] ), FILTER ( ALL ( DimDate ), YEAR ( DimDate[Date] ) = YEAR ( currentDate ) && DimDate[Date] <= currentDate ) )
Regards
Hi @Anonymous,
If I understand you correctly, the formula below should work in your scenario.
Number_of_weeks_YTD = VAR currentDate = TODAY () RETURN CALCULATE ( DISTINCTCOUNT ( DimDate[week_number] ), FILTER ( ALL ( DimDate ), YEAR ( DimDate[Date] ) = YEAR ( currentDate ) && DimDate[Date] <= currentDate ) )
Regards
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 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |