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.
Hi All,
I am struggling with one calculation. I have weekly data mapped to Sundays.
The date contains data of the prior week.
Data-
Scheme | Start Date | End Date |
Scheme1 | 5-Jul-20 | 2-Aug-20 |
Scheme2 | 5-Jul-20 | 19-Jul-20 |
Scheme3 | 12-Jul-20 | 9-Aug-20 |
If a scheme is running for 2 weeks, for both weeks there should be a count.
Example Output -
Week Date | Scheme1 | Scheme2 | Scheme3 | Total |
5-Jul-20 | 1 | 1 | 2 | |
12-Jul-20 | 1 | 1 | 1 | 3 |
19-Jul-20 | 1 | 1 | 1 | 3 |
26-Jul-20 | 1 | 1 | 2 | |
2-Aug-20 | 1 | 1 | 2 | |
9-Aug-20 | 1 | 1 |
Please help with how do I calculate so that I can get proper count.
Also note I have some data which has start and end date an year apart.
Solved! Go to Solution.
Hi, @raimon ;
You could create a date table about every sunday, then create a measure to calculate the count like below:
1.create a date table.
Date = FILTER( ADDCOLUMNS( CALENDAR(DATE(2020,7,1),DATE(2020,9,1)),"weekday",WEEKDAY([Date],2)),[weekday]=7)
2.create a measure.
Measure =
var _value=CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Scheme]=MAX('Table'[Scheme])&&[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date])))
return IF(ISFILTERED('Table'[Scheme]),_value,CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))))
then use a matrix and the final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @raimon ;
You could create a date table about every sunday, then create a measure to calculate the count like below:
1.create a date table.
Date = FILTER( ADDCOLUMNS( CALENDAR(DATE(2020,7,1),DATE(2020,9,1)),"weekday",WEEKDAY([Date],2)),[weekday]=7)
2.create a measure.
Measure =
var _value=CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Scheme]=MAX('Table'[Scheme])&&[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date])))
return IF(ISFILTERED('Table'[Scheme]),_value,CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))))
then use a matrix and the final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Disconnected date table with a measure like :
MeasureT = VAR _dt = MAX(DatesTable[Date])
RETURN
COUNTROWS(FILTER(TableG, TableG[Start Date] <= _dt && TableG[End Date] >= _dt))
Put the date field in the rows of a matrix, scheme in the columns and measure in the Values.
You might need to edit the conditions in the matrix to suit (<,<= etc).
Filter the dates table to get the rows you need (Sundays, I think).
Also Scheme 1 and 3 look wrong in the desired table.
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |