Forum Discussion
KPI count each week (YTD)
Hi there,
I want to count how many times the total value of a week is beneath the KPI (<90%).
Currently this works:
Calculate measure based on weeknumber = 1
( I have added weeknum behind the rows values to indicate which week the value is)
I was thinking about creating 52 weeks VAR, but there must be a smarter way?
So I want to count ALL the weeks, IF it has value (if a row has this weeknumber)
calculate measure of all the rows of this week, IF < 90% then count 1 else 0.
How can I achieve this the easiest?
Hi PowerKoen ,
According to your data, you can create a calendar table named DateKey using DAX below.
DateKey = CALENDARAUTO()
Then, create measure to count how many times the total value of a week is beneath the KPI (<90%).
CountPerDay =COUNTX(ALLSELECTED(Table1),IF( DIVIDE(CALCULATE(SUM('Table1'[Value]),FILTER(ALLSELECTED('Table1',WEEKNUM('Table1'[Date])=WEEKNUM(MAX('DateKey'[Date])))),SUM('Table1'[Value]))<0.9,1,BLANK()))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-xicaiCommunity Support
Hi PowerKoen ,
According to your data, you can create a calendar table named DateKey using DAX below.
DateKey = CALENDARAUTO()
Then, create measure to count how many times the total value of a week is beneath the KPI (<90%).
CountPerDay =COUNTX(ALLSELECTED(Table1),IF( DIVIDE(CALCULATE(SUM('Table1'[Value]),FILTER(ALLSELECTED('Table1',WEEKNUM('Table1'[Date])=WEEKNUM(MAX('DateKey'[Date])))),SUM('Table1'[Value]))<0.9,1,BLANK()))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.