Forum Discussion

utsavlexmark's avatar
utsavlexmark
Helper III
7 years ago
Solved

Looking for Tips on Storing Data

Hello, I am an Digital Marketing Professional and recently started using Power BI for representing data to the stackholders. Currently I am working with a simple data which I need to present with lo...
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi utsavlexmark

    Create measures in your table

    measure =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) 
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure1 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 1
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure2 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 2
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure3 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 3
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)
    current week = IF([measure]<>BLANK(),[measure],0)

current week-1 = IF([measure1]<>0,[measure1],0)

current week-2 = IF([measure2]<>BLANK(),[measure2],0)

current week-3 = IF([measure3]<>BLANK(),[measure3],0)

     

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi utsavlexmark

Create measures in your table

measure =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) 
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure1 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 1
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure2 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 2
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)

measure3 =
CALCULATE (
    SUM ( Sheet2[Totquantity] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Mktname], Sheet2[Partnumber] ),
        [Week]
            = SELECTEDVALUE ( Sheet2[Week] ) - 3
            && [Year] = SELECTEDVALUE ( Sheet2[Year] )
    )
)
current week = IF([measure]<>BLANK(),[measure],0)

current week-1 = IF([measure1]<>0,[measure1],0)

current week-2 = IF([measure2]<>BLANK(),[measure2],0)

current week-3 = IF([measure3]<>BLANK(),[measure3],0)

 

 

Best Regards

Maggie

utsavlexmark's avatar
utsavlexmark
Helper III
7 years ago

Hello Maggie,

Just wow; I could not explain - how happy I am. I was knocking my head in wall to do this for last one month.

Thank you very much.

Not only, this trick will help many more in future.

Thanks from all of them in Advance.

Regards

Utsav