Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Quarter | Count | Week | Year |
Q1 | 23 | 2 | FY20 |
Q2 | 21 | 2 | FY20 |
Q3 | 44 | 31 | FY19 |
Q4 | 21 | 23 | FY19 |
Q4 | 11 | 1 | FY19 |
Q4 | 34 | 2 | FY19 |
Example above:
My FY20, Q1 , Week 2 requires values from FY19, Q4, Week 1 & 2 (Weeks to date)
This works
CALCULATE(
COUNT(TABLE1[Name]),
FILTER(all(Fisc_Cal), Fisc_Cal[Fiscal_Qtr] = 4 &&
Corp_Fisc_Cal[Fiscal_Year] = max( Fisc_Cal[Fiscal_Year])-1 && Fisc_Cal[Qtr_Wk] < 3)),
but instead of <3, i need to dynamically count the earlier weeks fromt the currently selected week. Nothing seems to work.
Help !!!
Solved! Go to Solution.
Hi @jogenpatel ,
To update your measure as below.
QoQ% = --min(Corp_Fisc_Cal[Fiscal_Qtr]) VAR cQtr = MIN ( Corp_Fisc_Cal[Fiscal_Qtr] ) VAR cQwk = MIN ( Corp_Fisc_Cal[Qtr_Wk] ) VAR cYr = MIN ( Corp_Fisc_Cal[Fiscal_Year] ) VAR _year = YEAR ( TODAY () ) + 1 RETURN VAR total_LQtr = IF ( cQtr = 1 && cYr = _year, CALCULATE ( DISTINCTCOUNT ( vw_Bolt_Helpdesk_PBI[Name] ), FILTER ( ALL ( Corp_Fisc_Cal ), Corp_Fisc_Cal[Fiscal_Qtr] = 1 && Corp_Fisc_Cal[Fiscal_Year] = MAX ( Corp_Fisc_Cal[Fiscal_Year] ) - 1 ) ), IF ( cQtr = 1, CALCULATE ( DISTINCTCOUNT ( vw_Bolt_Helpdesk_PBI[Name] ), FILTER ( ALL ( Corp_Fisc_Cal ), Corp_Fisc_Cal[Fiscal_Qtr] = 4 && Corp_Fisc_Cal[Fiscal_Year] = MAX ( Corp_Fisc_Cal[Fiscal_Year] ) - 1 ) ), CALCULATE ( DISTINCTCOUNT ( vw_Bolt_Helpdesk_PBI[Name] ), FILTER ( ALL ( Corp_Fisc_Cal ), Corp_Fisc_Cal[Fiscal_Qtr] = MAX ( Corp_Fisc_Cal[Fiscal_Qtr] ) - 1 && Corp_Fisc_Cal[Fiscal_Year] = MAX ( Corp_Fisc_Cal[Fiscal_Year] ) ) ) ) ) RETURN total_LQtr
Please find the pbix as attached.
Regards,
Frank
Hi @jogenpatel ,
To create a calculated table as below.
week = GENERATESERIES(1,100,1)
Add the value column of the week table in a slicer.Then we can update your formula as below.
CALCULATE( COUNT(TABLE1[Name]), FILTER(all(Fisc_Cal), Fisc_Cal[Fiscal_Qtr] = 4 && Corp_Fisc_Cal[Fiscal_Year] = max( Fisc_Cal[Fiscal_Year])-1 && Fisc_Cal[Qtr_Wk] < SELECTEDVALUE(week[Value])))
If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.
Regards,
Frank
Hi @v-frfei-msft (Frank),
Thank you for replying.
We do not need to add the week to the slicer as that calculation will apply only for the current Qtr. I've explained my scenario below.
The QoQ calculation takes the total of the earlier quarter and computes with the following quarter. However, if the Quarter is current, it will take the counts of the completed weeks of that current quarter Vs the same number of weeks from the earlier quarter instead of taking the total count from the earlier week. I've attached the sample PBI below:
Here is the link to the Sample pbix:
https://1drv.ms/u/s!ApqSgyl2itergSJlAjXGQMyhUEPa
thank you again!!
Hi @jogenpatel ,
To update your measure as below.
QoQ% = --min(Corp_Fisc_Cal[Fiscal_Qtr]) VAR cQtr = MIN ( Corp_Fisc_Cal[Fiscal_Qtr] ) VAR cQwk = MIN ( Corp_Fisc_Cal[Qtr_Wk] ) VAR cYr = MIN ( Corp_Fisc_Cal[Fiscal_Year] ) VAR _year = YEAR ( TODAY () ) + 1 RETURN VAR total_LQtr = IF ( cQtr = 1 && cYr = _year, CALCULATE ( DISTINCTCOUNT ( vw_Bolt_Helpdesk_PBI[Name] ), FILTER ( ALL ( Corp_Fisc_Cal ), Corp_Fisc_Cal[Fiscal_Qtr] = 1 && Corp_Fisc_Cal[Fiscal_Year] = MAX ( Corp_Fisc_Cal[Fiscal_Year] ) - 1 ) ), IF ( cQtr = 1, CALCULATE ( DISTINCTCOUNT ( vw_Bolt_Helpdesk_PBI[Name] ), FILTER ( ALL ( Corp_Fisc_Cal ), Corp_Fisc_Cal[Fiscal_Qtr] = 4 && Corp_Fisc_Cal[Fiscal_Year] = MAX ( Corp_Fisc_Cal[Fiscal_Year] ) - 1 ) ), CALCULATE ( DISTINCTCOUNT ( vw_Bolt_Helpdesk_PBI[Name] ), FILTER ( ALL ( Corp_Fisc_Cal ), Corp_Fisc_Cal[Fiscal_Qtr] = MAX ( Corp_Fisc_Cal[Fiscal_Qtr] ) - 1 && Corp_Fisc_Cal[Fiscal_Year] = MAX ( Corp_Fisc_Cal[Fiscal_Year] ) ) ) ) ) RETURN total_LQtr
Please find the pbix as attached.
Regards,
Frank
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |