Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I have a table that looks like this and I want to create a measure to get the total number of days in the quarter (i.e. FY21-Q4) which is 30+31+30 = 91. Does anyone know how I can get 91?
FYI, Fiscal Quarter is a slicer in my dashboard page
Regards,
BK
Solved! Go to Solution.
Hi @Anonymous ,
You could create a calendar, then extract his FY-QUARTER column, and finally COUNT the rows of quarter, create measures by the following formula:
First create table:
Newtable = CALENDARAUTO(6)
Then create column:
FY-Qua = "FY"&RIGHT(IF(MONTH([Date])>=7,YEAR([Date])+1,YEAR([Date])),2)&"-Q"&SWITCH(QUARTER([Date]),3,1,4,2,1,3,2,4)
Last create measure:
Quartertotal = CALCULATE(COUNTROWS('Newtable'),FILTER('Newtable',[FY-Qua] in VALUES('Table'[Fiscal Quarter])))
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 @Anonymous ,
You could create a calendar, then extract his FY-QUARTER column, and finally COUNT the rows of quarter, create measures by the following formula:
First create table:
Newtable = CALENDARAUTO(6)
Then create column:
FY-Qua = "FY"&RIGHT(IF(MONTH([Date])>=7,YEAR([Date])+1,YEAR([Date])),2)&"-Q"&SWITCH(QUARTER([Date]),3,1,4,2,1,3,2,4)
Last create measure:
Quartertotal = CALCULATE(COUNTROWS('Newtable'),FILTER('Newtable',[FY-Qua] in VALUES('Table'[Fiscal Quarter])))
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.
Get a distinct list of "week" values (which actually seem to be months), then get the average of days for each of them, and finally add them up.
Or, use a proper calendar table based on days, not on weeks.
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |