Forum Discussion

JamesLeach's avatar
JamesLeach
Frequent Visitor
8 years ago
Solved

Change in Values over Time - Calculated Column?

I'll start out by saying I'm pretty new to Power BI and have clearly gotten in over my head with what I thought would be a relatively easy first 'real' use case...    This is a similar question to ...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Here is the result i got.  You may download my PBI file from here.

     

    Hope this helps.

     

  • JamesLeach's avatar
    JamesLeach
    7 years ago

    Okay, for anyone that comes across this thread in the future...  

     

    Removal of the 'index' column was a bad idea. 

     

    While it works without it, using the Date, as an index, memory consumption jumps through the roof.   So much so that with the addition of a couple more weeks of data my PC couldn't refresh.   And then, with a couple more weeks, the Power BI Service couldn't refresh.  

     

    Here is the DAX with the Index column:  

     

    QtyAvailableChange =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Data[QtyAvailable],
            Data[Date], CALCULATE (
                MAX ( Data[Date] ),
                FILTER (
                    Data,
                    Data[Index] < EARLIER ( Data[Index] )
                        && Data[SKU] = EARLIER ( Data[SKU] )
                        && Data[Location] = EARLIER ( Data[Location] )
                )
            ),
            Data[SKU], Data[SKU],
            Data[Location], Data[Location]
        )
    ),
    0,
    [QtyAvailable]
        - LOOKUPVALUE (
            Data[QtyAvailable],
            Data[Date], CALCULATE (
                MAX ( Data[Date] ),
                FILTER (
                    Data,
                    Data[Index] < EARLIER ( Data[Index] )
                        && Data[SKU] = EARLIER ( Data[SKU] )
                        && Data[Location] = EARLIER ( Data[Location] )
                )
            ),
            Data[SKU], Data[SKU],
            Data[Location], Data[Location]
        )
)

    Do it this way - not the way I previously referenced.   

     

    To create the index column I added Index as a custom column in Query Editor with the M query: 

     

    Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))

     

    Where YYYY,MM,DD is the Year, Month and Day of my earliest Date record.  

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Here is the result i got.  You may download my PBI file from here.

 

Hope this helps.

 

  • JamesLeach's avatar
    JamesLeach
    Frequent Visitor
    8 years ago

    Thank you very much, Ashish Mathur!  This was very helpful and informative.  

     

    I did make a couple changes to the calculation.   The sign of the number seemed backwards to me so I changed the order of the calculation.    I also removed the dependance on the index column.   

     

    Here is the modified DAX: 

     

    QtyAvailableChange =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                Data[QtyAvailable],
                Data[Date], CALCULATE (
                    MAX ( Data[Date] ),
                    FILTER (
                        Data,
                        Data[Date] < EARLIER ( Data[Date] )
                            && Data[SKU] = EARLIER ( Data[SKU] )
                            && Data[Location] = EARLIER ( Data[Location] )
                    )
                ),
                Data[SKU], Data[SKU],
                Data[Location], Data[Location]
            )
        ),
        0,
        [QtyAvailable]
            - LOOKUPVALUE (
                Data[QtyAvailable],
                Data[Date], CALCULATE (
                    MAX ( Data[Date] ),
                    FILTER (
                        Data,
                        Data[Date] < EARLIER ( Data[Date] )
                            && Data[SKU] = EARLIER ( Data[SKU] )
                            && Data[Location] = EARLIER ( Data[Location] )
                    )
                ),
                Data[SKU], Data[SKU],
                Data[Location], Data[Location]
            )
    )

     

    I've experiemented with a vastly expanded dataset and I don't see any issues.

     

    Thank you again for your help!   

     

    Edited:  Changed DAX formatting and added clarification on the additional testing.

     

    • JamesLeach's avatar
      JamesLeach
      Frequent Visitor
      7 years ago

      Okay, for anyone that comes across this thread in the future...  

       

      Removal of the 'index' column was a bad idea. 

       

      While it works without it, using the Date, as an index, memory consumption jumps through the roof.   So much so that with the addition of a couple more weeks of data my PC couldn't refresh.   And then, with a couple more weeks, the Power BI Service couldn't refresh.  

       

      Here is the DAX with the Index column:  

       

      QtyAvailableChange =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Data[QtyAvailable],
            Data[Date], CALCULATE (
                MAX ( Data[Date] ),
                FILTER (
                    Data,
                    Data[Index] < EARLIER ( Data[Index] )
                        && Data[SKU] = EARLIER ( Data[SKU] )
                        && Data[Location] = EARLIER ( Data[Location] )
                )
            ),
            Data[SKU], Data[SKU],
            Data[Location], Data[Location]
        )
    ),
    0,
    [QtyAvailable]
        - LOOKUPVALUE (
            Data[QtyAvailable],
            Data[Date], CALCULATE (
                MAX ( Data[Date] ),
                FILTER (
                    Data,
                    Data[Index] < EARLIER ( Data[Index] )
                        && Data[SKU] = EARLIER ( Data[SKU] )
                        && Data[Location] = EARLIER ( Data[Location] )
                )
            ),
            Data[SKU], Data[SKU],
            Data[Location], Data[Location]
        )
)

      Do it this way - not the way I previously referenced.   

       

      To create the index column I added Index as a custom column in Query Editor with the M query: 

       

      Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))

       

      Where YYYY,MM,DD is the Year, Month and Day of my earliest Date record.  

      • gwright15's avatar
        gwright15
        Helper I
        7 years ago
        Sorry for dragging up an old thread, but i can't seem to get the index column working.
        &nbsp;
        Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))
        I've receiving the error:
        &nbsp;Expression.Error: The name 'YYYY' wasn't recognized. Make sure it's spelled correctly.

        Edit - sorry me being daft - I now follow!
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      You are welcome.

  • GokilaRaviraj's avatar
    GokilaRaviraj
    Helper II
    1 year ago

    Hi Ashish_Mathur Greg_Deckler 

    I have 2 input tables shown below

    CFY refers to Current FinYear and NFY refers to Next Finyear

    Total Income

    CompanyOctCFYNovCfyDecCFYJanNfyFebnfyMarnfyAprnfymaynfyjunnfyJulnfyAugnfySepnfyOctnfyNovnfyDecnfy
    A101010101010101010101010101010
    B101010101010101010101010101010
    C101010101010101010101010101010
    D101010101010101010101010101010
    E101010101010101010101010101010
    F101010101010101010101010101010

    Admin Cost

    CompanyOctCFYNovCfyDecCFYJanNfyFebnfyMarnfyAprnfymaynfyjunnfyJulnfyAugnfySepnfyOctnfyNovnfyDecnfy
    A202020202020202020202020202020
    B202020202020202020202020202020
    C202020202020202020202020202020
    D202020202020202020202020202020
    E202020202020202020202020202020
    F202020202020202020202020202020

    I am showing months from current month + remaining months of the year + next year all the months. This will change dynamically. 

     

    Output Table

    VAT

    CompanyJancfyFebcfyMarcfyAprcfymaycfyjuncfyJulcfyAugcfySepcfyOctcfyNovcfyDeccfyJannfyFebnfyMarnfyAprnfymaynfyjunnfyJulnfyAugnfySepnfyOctnfyNovnfyDecnfy
    A022.50022.50022.50022.510022.50022.50022.50022.50
    E022.50022.50022.50022.510022.50022.50022.50022.50
    D022.50022.50022.50022.510022.50022.50022.50022.50

    This calculations has to be done for every second month of the quarter. ie) Feb, May, Aug, Nov.   and It has to be done only when month is January. February value will be a manual input and the rest of all the months needs a calculation.

     

    When month is jan, Report shows value from current year jan to next year december.

     

    May = sum(total income of Jan+ feb+ mar)*25% + sum(admin cost of Jan + feb+ mar)*25%

    Aug =sum(total income of Apr+ May+ Jun)*25% + sum(admin cost of Apr+ May+ Jun)*25%

    Nov =sum(total income of Jul+ Aug+ Sep)*25% + sum(admin cost of Jul+ Aug+ Sep)*25%

    Febnextyear= sum(total income of oct+ Nov+ Dec)*25% + sum(admin cost of oct+ Nov+ Dec)*25%

    Maynextyear = sum(total income of Jannextyear+ febnextyear+ marnextyear)*25% + sum(admin cost of Jannextyear + febnextyear+ marnextyear)*25%

    Augnextyear=sum(total income of Aprnextyear+ Maynextyear+ Junnextyear)*25% + sum(admin cost of Aprnextyear+ Maynextyear+ Junnextyear)*25%

    Novnextyear =sum(total income of Julnextyear+ Augnextyear+ Sepnextyear)*25% + sum(admin cost of Julnextyear+ Augnextyear+ Sepnextyear)*25%

     

    Here is the calculated measure which i got, 

    VAR a =
        GENERATESERIES ( 5, 23, 3 )
    VAR b =
        SELECTEDVALUE ('VATMonthCommonYear'[Index]) - 4
    VAR c =
        SELECTEDVALUE ('VATMonthCommonYear'[Index]) - 2
    VAR d =
        CALCULATE (
            SUM ('VATTotalIncome'[Value]),
            ALLSELECTED ('VATMonthCommonYear'[MonthYear]),
            'VATMonthCommonYear'[Index] <= c,
            'VATMonthCommonYear'[Index] >= b
        )
    VAR e =
        CALCULATE (
            SUM ('VATAdminCost'[Value]),
            ALLSELECTED ('VATMonthCommonYear'[MonthYear]),
            'VATMonthCommonYear'[Index] <= c,
            'VATMonthCommonYear'[Index] >= b
        )
    RETURN
        IF (SELECTEDVALUE ('VATMonthCommonYear'[Index]) IN a, d * 0.25 + e * 0.25, 0 )
     
    This is the logic which works fine. But I wanted this as a calculated column, since i want to use this column in further to do some appendings.
     
    If you have an idea on this, could you please help me on this ?