Forum Discussion

HotelRM's avatar
HotelRM
Regular Visitor
9 years ago

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

  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,...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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.:smileyhappy:

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

  • HotelRM's avatar
    HotelRM
    Regular Visitor
    9 years ago

    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.

     

     

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      HotelRM

       

      hi, you can use sameperiodlastyear()

       

      Example:

       

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

      • HotelRM's avatar
        HotelRM
        Regular Visitor
        9 years ago

        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.