Forum Discussion

jpt1228's avatar
jpt1228
Icon for Responsive Resident rankResponsive Resident
9 years ago

Compare value from 364 days ago

Hello, I have data that is reported every 28 days or 13 times per year. I need to compare the current value with the previous value from last year (reported 364 days prior). Since the calendar dates ...
Need Help
OpenDataLab's avatar
OpenDataLab
Icon for Helper II rankHelper II
9 years ago

The easiest way I have found to do this is by adding a day overall number, basically a unique row id 1,2,3,4,5 etc....I use this to navigate back a froward through my date range.

 

LY TDP =
CALCULATE (
    SUM ( TDP[TDP] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Day Over All]
            = MAX ( 'Date'[Day Over All] ) - 364
    )
)

  • jpt1228's avatar
    jpt1228
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    Hi OpenDataLab - Are you saying bring in the day of the year for each date in the DateDim 1-365 not including a leap year?

     

    Thanks

    • OpenDataLab's avatar
      OpenDataLab
      Icon for Helper II rankHelper II
      9 years ago

      No.  Just add an identity field that keeps on going from the first day of your date dimension until the last day. e.g if your date dimension had 2 years worth of data then you would hve values from 1 through to 728.

       

       

       

      • jpt1228's avatar
        jpt1228
        Icon for Responsive Resident rankResponsive Resident
        9 years ago

        I have multiple dates for multiple customers. Im pretty new to PoweerBI. Would this be an index column of the DimDate table? This is a sample of my source table. I have transformed the Time Description into an actual date.

         

        Time DescriptionGeographyBrandTDP
        Quad End 2015-Aug-16TOTAL US - SPECIALTY GOURMET CHANNELCompany A117
        Quad End 2015-Aug-16TOTAL US - MULOCompany A19
        Quad End 2015-Aug-16TOTAL US - NATURAL CHANNELCompany B14
        Quad End 2015-Aug-16TOTAL US - MULOCompany B1
        Quad End 2015-Aug-16TOTAL US - SPECIALTY GOURMET CHANNELCompany C5
        Quad End 2015-Aug-16TOTAL US - MULOCompany C2
        Quad End 2015-Dec-06TOTAL US - SPECIALTY GOURMET CHANNELCompany A118
        Quad End 2015-Dec-06TOTAL US - MULOCompany A20
        Quad End 2015-Dec-06TOTAL US - NATURAL CHANNELCompany B18
        Quad End 2015-Dec-06TOTAL US - MULOCompany B0
        Quad End 2015-Dec-06TOTAL US - SPECIALTY GOURMET CHANNELCompany C5
        Quad End 2015-Dec-06TOTAL US - MULOCompany C2
        Quad End 2015-Jul-19TOTAL US - SPECIALTY GOURMET CHANNELCompany A116
        Quad End 2015-Jul-19TOTAL US - MULOCompany A18
        Quad End 2015-Jul-19TOTAL US - NATURAL CHANNELCompany B16
        Quad End 2015-Jul-19TOTAL US - MULOCompany B1
        Quad End 2015-Jul-19TOTAL US - SPECIALTY GOURMET CHANNELCompany C5
        Quad End 2015-Jul-19TOTAL US - MULOCompany C2
        Quad End 2015-Nov-08TOTAL US - SPECIALTY GOURMET CHANNELCompany A117