locked
First and Last Value in Partition RRS feed

  • Question

  • Hi,

    I have a tabular model with the following table:

    each payee can have many stores. The tables contains the store monthly volume for months 1-5 of 2017 and 2018.

    i want to build 2 measures one for 2017 and one for 2018 calculating the sum of the volume amount for stores that were active in both 2017 and 2018 (active = had volume in one of the months of the relevant year) .

    I thought of adding a 2 calculated column for the first load and last load on the store level and using the following measure:

    Volume 2017 = calculate(sum(Monthly Volume),
                                         year(Month Date) = 2017, 
                                         Is Active on 2017 = 1,
                                         Is Active on 2018 = 1)

    What i don't know is how to create the calculated columns for activity indication.

    Any Ideas how to achieve this or other suggestions?

    Thanks!



    • Edited by Udianco Tuesday, June 19, 2018 1:33 PM
    Tuesday, June 19, 2018 1:30 PM

Answers

  • Hi Udianco,

    Thanks for your response.

    >>>>I mean spreading the Is_Active_on_2017 indication for all the store records of 2017 in the fact table.
    In this scenario, you can try below DAX formula for calculated column [Is Active on 2017]:

    [Is Active on 2017] =
    IF (
        COUNTROWS (
            FILTER (
                'YourFactTable',
                YEAR ( 'YourFactTable'[Month Date] ) = 2017
                    && 'YourFactTable'[Store ID] = EARLIER ( 'YourFactTable'[Store ID] )
            )
        )
            > 0,
        1
    )

    please see below sample DAX formula with your sample data, the fact table name is Sales:

    Is Active on 2017 = 
    IF (
        COUNTROWS (
            FILTER (
                'Sales',
                YEAR ( 'Sales'[Month Date] ) = 2017
                    && 'Sales'[Store ID] = EARLIER ( 'Sales'[Store ID] )
            )
        )
            > 0,
        1
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Udianco Wednesday, June 20, 2018 5:16 AM
    Wednesday, June 20, 2018 4:25 AM

All replies

  • Hi Udianco,

    Thanks for your question.

    >>>What i don't know is how to create the calculated columns for activity indication.
    Assuming you have created a Store dimension table in your SSAS Tabular Model, you can then create a calculated column called [Is Active on 2017] in your Store dimension table as below DAX formula:

    [Is Active on 2017] =
    IF (
        COUNTROWS (
            FILTER (
                'YourFactTable',
                YEAR ( 'YourFactTable'[Month Date] ) = 2017
                    && 'YourFactTable'[Store ID] = EARLIER ( 'Store'[Store ID] )
            )
        )
            > 0,
        1
    )
    You can create another calculated column called [Is Active on 2018] in your Store dimension table just like the DAX formula used for calculated column [Is Active on 2017]. 

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 20, 2018 3:05 AM
  • Thanks Willson!

    i assume from your answer that you can't achieve this without creating a store dimension?

    I mean spreading the Is_Active_on_2017 indication for all the store records of 2017 in the fact table.

    Wednesday, June 20, 2018 3:46 AM
  • Hi Udianco,

    Thanks for your response.

    >>>>I mean spreading the Is_Active_on_2017 indication for all the store records of 2017 in the fact table.
    In this scenario, you can try below DAX formula for calculated column [Is Active on 2017]:

    [Is Active on 2017] =
    IF (
        COUNTROWS (
            FILTER (
                'YourFactTable',
                YEAR ( 'YourFactTable'[Month Date] ) = 2017
                    && 'YourFactTable'[Store ID] = EARLIER ( 'YourFactTable'[Store ID] )
            )
        )
            > 0,
        1
    )

    please see below sample DAX formula with your sample data, the fact table name is Sales:

    Is Active on 2017 = 
    IF (
        COUNTROWS (
            FILTER (
                'Sales',
                YEAR ( 'Sales'[Month Date] ) = 2017
                    && 'Sales'[Store ID] = EARLIER ( 'Sales'[Store ID] )
            )
        )
            > 0,
        1
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Udianco Wednesday, June 20, 2018 5:16 AM
    Wednesday, June 20, 2018 4:25 AM
  • Thanks for your help Willson, worked perfectly!
    Wednesday, June 20, 2018 5:17 AM