Last Year measures with a NON traditional Fiscal calendar (4-4-5)

[ocabrejo]

Hi everyone,

I'm having a hard time trying to find a solution with my LY and LYTD measures, because of our fiscal calendar doesn't start the 1st and ends on the 30/31st of every month. To better explain this, here is a screenshot of my different measures in separate visuals listed by calendar dates.

1. The first thing to notice, it that I'm removing the YEAR filtering on the 2nd and 4th visual and for the 2nd visual, I've filtered it to show only 2019,. All this, so that it allows me compare the data without considering the YEAR slicer (2020).
2. Second, my Fiscal period from year to year have different start/end dates. For 2020 and 2019, one starts on 2020/02/03 and the other on 2019/01/28

Now, here are my measures for each visual (left to right) :

1. Total Sales = SUM('Fact Sales'[Net Price])+0
2. Total Sales = SUM('Fact Sales'[Net Price])+0
3. Total Sales LY YTD = CALCULATE([Total Sales YTD], SAMEPERIODLASTYEAR('Dim Date'[Date]))
   1. Total Sales YTD = CALCULATE(TOTALYTD([Total Sales], 'Dim Date'[Date]))
4. Total Sales LY YTD VALID = CALCULATE([Total Sales YTD],'Dim Date'[Year]=YEAR(NOW())-1)

Total Sales LY YTD VALID almost works for me, BUT it's not dynamic, meaning if I readd the filtering (2020), it won't show anything... What I'm going for is a measure that will allow me to get my result according to the slicers, for e.g.: 

• Total Sales LY YTD VALID = CALCULATE([Total Sales YTD],'Dim Date'[Year]=(YearSELECTEDinSlicer)-1)

 But, obviously, this doesn't work. Any ideas?

[v-shex-msft]

Hi @ocabrejo,

Time intelligence function not suitable to nested or do customize with its calculation ranges.
In my opinion, I'd like to suggest you use DATE function to manually defined filter range to calculate, it should more agility and customizable. (YTD logic: records who has same year and date less than or equal to the current date)

YTD Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && [Date] <= currDate
        )
    )

If you confused about coding formula, please share some dummy data with a similar data structure to test.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[ocabrejo]

The time intelligence functions will not work for me with a 4-5-4 calendar unfortunately.

[v-shex-msft]

Hi @ocabrejo,

I think you can consider write formula based on fiscal year and date fields, it also works for 4-5-4 calendar. (measure formula use the fiscal year to lock on the calculate date range and use date field as rolling condition)

YTD Measure =
VAR currDate =
    MAX ( Table[Date] )
VAR currFYear =
    MAX ( Calendar[Fiscal Year] )
VAR dateRange =
    CALCULATETABLE (
        VALUE ( Calendar[Date] ),
        FILTER ( ALLSELECTED ( Calendar ), [Fiscal Year] = currFYear )
    )
RETURN
    CALCULATE (
        SUM ( Table[Net Price] ),
        FILTER ( ALLSELECTED ( Table ), [Date] IN dateRange && [Date] <= currDate )
    )

LYTD Measure =
VAR currDate =
    MAX ( Table[Date] )
VAR currFYear =
    MAX ( Calendar[Fiscal Year] )
VAR dateRange =
    CALCULATETABLE (
        VALUE ( Calendar[Date] ),
        FILTER ( ALLSELECTED ( Calendar ), [Fiscal Year] = currFYear-1 )
    )
RETURN
    CALCULATE (
        SUM ( Table[Net Price] ),
        FILTER ( ALLSELECTED ( Table ), [Date] IN dateRange && [Date] <= currDate )
    )

Regards,

Xiaoxin Sheng

[ocabrejo]

Thank you for responding @v-shex-msft Xiaoxin Sheng,

I've uploaded a dummy dataset and .pbix with the exact same Date dimension and the latest LY measure suggestion.

https://drive.google.com/drive/folders/14Q8SLMHOZV2qwaRtS9e2Qhvzouzu9o7A?usp=sharing

Let me know if you see anything that works out on your end.

Much appreciated

Oliver

[v-shex-msft]

HI @ocabrejo,

You can try below measure formula, I extract the 'fiscal start date' year as a condition to filter records:

LY Measure = 
VAR currDate =
    MAX ( 'Dim Date'[Date] )
VAR currFYear =
   YEAR( MAX ( 'Dim Date'[FiscalStartDate] ))
VAR dateRange =
    CALCULATETABLE (
        VALUES('Dim Date'[Date]),
        FILTER ( ALLSELECTED ( 'Dim Date' ), YEAR('Dim Date'[FiscalStartDate]) = currFYear-1 )
    )
RETURN
    CALCULATE (
        SUM ( 'Fact Sales'[Total] ),
        FILTER ( ALLSELECTED ( 'Fact Sales' ), 'Fact Sales'[Date] IN dateRange && 'Fact Sales'[Date] <= currDate )
    )

Regards,

Xiaoxin Sheng

[ocabrejo]

Hi @v-shex-msft ,

I've tried your new formula, but it's not working. I'm getting empty results/blanks.

I've updated the Google Drive.

Thank you

Oliver

[ocabrejo · Accepted solution]

I have found the solution for any of you that might be interested : 

Total Sales LY = 
CALCULATE(
        [Total Sales]
        ,FILTER(
            ALL('Dim Date')
            ,'Dim Date'[Year]=VALUES('Dim Date'[Year])-1
            &&'Dim Date'[Fiscal Period]<=MAX('Dim Date'[Fiscal Period] )
            &&'Dim Date'[Fiscal Period]>=MIN('Dim Date'[Fiscal Period] )
    )
)