Getting range of week numbers from earlier year's week

[jogenpatel]

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 !!!

[v-frfei-msft]

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

[jogenpatel]

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!!

[v-frfei-msft · Accepted 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

[jogenpatel]

Brilliant @v-frfei-msft (Frank). thank you very much!!!