cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jogenpatel Frequent Visitor
Frequent Visitor

Getting range of week numbers from earlier year's week

 

 

QuarterCountWeekYear
Q1232FY20
Q2212FY20
Q34431FY19
Q42123FY19
Q4111FY19
Q4342FY19

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Getting range of week numbers from earlier year's week

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

2222.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Getting range of week numbers from earlier year's week

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jogenpatel Frequent Visitor
Frequent Visitor

Re: Getting range of week numbers from earlier year's week

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.

 

Untitled.png

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

Community Support Team
Community Support Team

Re: Getting range of week numbers from earlier year's week

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

2222.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jogenpatel Frequent Visitor
Frequent Visitor

Re: Getting range of week numbers from earlier year's week

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