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.
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
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |