cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Sameperiodlastyear custom hierarchy 4 weeks calendar.

Hi all,

I need help with a custom hierarchy calendar. I have a 4 weeks per period calendar with 13 periods so 52 weeks each year. Uploaded test.pbix here for the simple sample data. https://1drv.ms/u/s!ArweH82sfYtEnHlOx78wkBU_I7Cu?e=HkxHzu

I already made a small visual with the year,period,week values, but the previous year,period,week does not work. I can not use the previousyear function because of the custom calendar.

 Compare with PY (measure) Expected PreviousYear 2021 2020 37 38 P202011 P201911 2 3 W202044 W202044 0 1

In the table above you see that my PY (measure) gives me 37 where expected is 38 for the year 2021 compared with 2020. The PY (measure) gives me 2 where expected is 3 for the P202011 compared with P201911. The PY (measure) gives me 0 where expected is 1 for the W202044 compared with W201944.

I think I just needs some custom hierarchy or something, but I need to have the "previous" year values for my period and weeks.

Who could help me out?

3 REPLIES 3
Super User

Subtract 1 from the "current" year.  In your filter set the period filter to a replaced text value including the new year

Let's say the Period is P202011 and the year is 2020

so the code would look like

PY =

var p = max(Period)

var y = max(year)
return calculate(sum(value),Period = substitute(p,format(y),format(y-1))

Resolver I

Thanks for your reply, does not work the way I would like it to work, besides that the function FORMAT needs at least 2 arguments.

I found a solution for the problem with the following measure:

AMOUNT SPLY =
VAR HuidigJaar =
MAX ( 'mdw DimDatum'[Jaar] )
VAR HuidigPeriodeNummer =
RIGHT ( SELECTEDVALUE ( 'mdw DimDatum'[JaarPeriode] ), 2 )
VAR HuidigWeek =
RIGHT ( SELECTEDVALUE ( 'mdw DimDatum'[JaarWeek] ), 2 )
VAR VorigJaar =
FORMAT ( HuidigJaar - 1, "0000" )
VAR VorigPeriodeNummer = "P" & VorigJaar & HuidigPeriodeNummer
VAR VorigWeek = "W" & VorigJaar & HuidigWeek
VAR Result =
IF (
SELECTEDVALUE ( 'mdw DimDatum'[JaarWeek] ) <> BLANK (),
CALCULATE (
[Amount],
FILTER (
ALL ( 'mdw DimDatum' ),
'mdw DimDatum'[JaarWeek] = VorigWeek
&& 'mdw DimDatum'[JaarPeriode] = VorigPeriodeNummer
&& 'mdw DimDatum'[Jaar] = VorigJaar
)
),
IF (
SELECTEDVALUE ( 'mdw DimDatum'[JaarPeriode] ) <> BLANK (),
CALCULATE (
[Amount],
FILTER (
ALL ( 'mdw DimDatum' ),
'mdw DimDatum'[JaarPeriode] = VorigPeriodeNummer
&& 'mdw DimDatum'[Jaar] = VorigJaar
)
),
IF (
SELECTEDVALUE ( 'mdw DimDatum'[Jaar] ) <> BLANK (),
CALCULATE (
[Amount],
FILTER ( ALL ( 'mdw DimDatum' ), 'mdw DimDatum'[Jaar] = VorigJaar )
),
CALCULATE (
[Amount],
ALL ( 'mdw DimDatum' )
)
)
)
)

RETURN
Result

I only have one problem now, I would like to add some timeintelligence like WeekToDate, PeriodToDate and YearToDate. But then I do not get the cumulatives.
Super User

If you have a custom calendar you need to create custom time intelligence functions. They will be especially hairy across year boundaries.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors