Reply
Frequent Visitor
Posts: 8
Registered: ‎03-14-2017

Create LY Data Column Based on the Existing Filter Selected for TY

[ Edited ]

 

Let's start by saying this is my very first post in the forum - I'm very new to Power BI

 

To give you some background information, we use Fiscal Year with Financial Periods of 4 weeks, 4 weeks, 5 weeks cycles with yearly exceptions where a period which could be period 1 or 12 is a 6 weeks period every few years. Periods start on Sundays and end on Saturdays but (obviously) they don't start on the first day of the month nor they finish on the last date.

 

I created a CalendarTable as follows to be able to filter data by Year, Fical Period, Quarters, etc:

Calendar TableCalendar Table

I have a graph that shows that uses the following data an succesfully filters data using the above table:

 

DataTableDataTable

I would now like to create a Graph or a table that will display the daily data for LY's period as selected on a slicer, here's an example of part of the graphs currently displayed for Period 6 Year 2013:

 

Capture1.PNG

 

So I would like to add to each date a BAR corresponding to each of the dates but for Period 6 in 2012.

 

Could anyone please shade some light? 

 

 

Highlighted
Super Contributor
Posts: 2,659
Registered: ‎07-17-2016

Re: Create LY Data Column Based on the Existing Filter Selected for TY

Hi @HotelRM,

 

According to your description above, the total days for different fiscal years may vary, right?

 

If that is the case, you may need to calculate the total days between the same period last fiscal year and current fiscal year first, then you can use DATEADD function to calculate the daily data for LY's period. 

 

The formula below to create a measure to calculate the daily data for LY's period is for your reference.Smiley Happy

LY's Room Sold =
VAR currentYear =
    MAX ( 'Date'[FiscalYear] )
VAR currentPeriod =
    MAX ( 'Date'[FiscalPeriod] )
VAR daysSinceLY =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            ALL ( 'Date' ),
            ( 'Date'[FiscalYear]
                = currentYear - 1
                && 'Date'[FiscalPeriod] >= currentPeriod )
                || ( 'Date'[FiscalYear] = currentYear
                && 'Date'[FiscalPeriod] < currentPeriod )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Data'[Room Sold] ),
        DATEADD ( 'Date'[Date], - daysSinceLY, DAY )
    )

 

Regards

Frequent Visitor
Posts: 8
Registered: ‎03-14-2017

Re: Create LY Data Column Based on the Existing Filter Selected for TY

[ Edited ]

Hi @v-ljerr-msft,

 

Many thanks for your quick response and suggestion, your solution would be great for to calculate the exact rooms sold for the same number of dates but I need something much simpler which I haven't been able to achieve yet.

 

All I need is to be able to get the rooms sold for the same period selected in the filter and for the "Previous Year" to the year currently selected in the filter. For example, if currenly my filter is on 2016 & Period 1 I would like to compare to rooms sold for Period 1 2015, regardless of how many weeks the period has in the different years.

 

 

Super Contributor
Posts: 1,243
Registered: ‎05-10-2016

Re: Create LY Data Column Based on the Existing Filter Selected for TY

@HotelRM

 

hi, you can use sameperiodlastyear()

 

Example:

 

SalesLY=Calculate(Sum(TableDemo[SalesColumn]),Sameperiodlastyear(CalendarTable[DateColumn]))

Frequent Visitor
Posts: 8
Registered: ‎03-14-2017

Re: Create LY Data Column Based on the Existing Filter Selected for TY

[ Edited ]

Hi @Vvelarde,

 

Thanks for the sugestion but no, I can't use sameperiodlastyear() for fiscal periods as set in my calendar table, I wish it was that easy!

 

Many thanks anyway.

 

 

Super Contributor
Posts: 1,243
Registered: ‎05-10-2016

Re: Create LY Data Column Based on the Existing Filter Selected for TY

@HotelRM

 

Ok, I think i have the solution, but take much time reproduce your tables.Please post sample Data.

 

Regards,

Frequent Visitor
Posts: 8
Registered: ‎03-14-2017

Re: Create LY Data Column Based on the Existing Filter Selected for TY

Hi Victor,

 

Not sure if I can upload them to the forum somehow but here they are in OneDrive:

FiscalCalendarSample 

DataSample

 

Many thanks for giving it a go!

 

 

Super Contributor
Posts: 1,243
Registered: ‎05-10-2016

Re: Create LY Data Column Based on the Existing Filter Selected for TY

@HotelRM

 

Hi, please check if this is the desire result

 

LYRooms.png

Frequent Visitor
Posts: 8
Registered: ‎03-14-2017

Re: Create LY Data Column Based on the Existing Filter Selected for TY

@Vvelarde

 

Thanks Victor. Rooms Sold for 2007 - P11 should be 1,153. You can see it directly in your table if you change the filter to 2007; your Rooms Sold for 2007 should be the same result as Rooms Sold LY when the filter is on 2008.

 

Thanks,

 

Super Contributor
Posts: 1,243
Registered: ‎05-10-2016

Re: Create LY Data Column Based on the Existing Filter Selected for TY

@HotelRM

 

I have a doubt

 

In your original post you want to see daily chart (Selected Year and Previous Year of each Day)

 

The sum by period is not exactly to a last year period because dates are different.

 

In 2007 period 11 start on 2/9 and in 2008 start in 31/08.