cancel
Showing results for
Did you mean:
Frequent Visitor

## Getting range of week numbers from earlier year's week

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

1 ACCEPTED SOLUTION

Accepted Solutions
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
```

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

## 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])))```

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.
Frequent Visitor

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

Hi @v-frfei-msft  (Frank),

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

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

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.
Frequent Visitor

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

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