Forum Discussion

svinayagam1984's avatar
svinayagam1984
Icon for Helper I rankHelper I
5 years ago
Solved

Need help with my measures

I need help with two things on my Power BI dashboard which I am building for my company   I have created four measures to report, New Customer # New Customer $ Lost Customer # Lost Customer $ ...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, svinayagam1984 

     

    I get it but the calculation is more complicated, because your needs are not suitable for multiple selection. If you want to do it, you need to create several virtual tables as var in the measure to calculate the previous week corresponding to the current week.

    Like this(example lost):

     

    flaglost = 
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( 'Date'[Date] ),
        "flag",
      
            VAR lastd =
                CALCULATE (
                    MAX(  Data[Date]),
                    FILTER ( ALL ( Data ), [Date] <EARLIER ( 'Date'[Date] ) )
                )
           
            VAR last =
                CALCULATETABLE (
                    DISTINCT ( Data[Customer] ),
                    FILTER ( ALL ( Data ), [Date] = lastd )
                )
            VAR cur =
                CALCULATETABLE (
                    DISTINCT ( Data[Customer] ),
                    FILTER ( ALL ( Data ), [Date] = EARLIER ( 'Date'[Date] ) )
                )
            RETURN
                IF ( MAX ( Data[Customer] ) IN EXCEPT (last, cur ), 1, 0 )
    )
RETURN
    IF ( SUMX ( tab, [flag] ) > 0, 1,0 )
    Measurelostamount = 
VAR d =
    MINX ( FILTER ( Data, [flaglost] = 1 ), [Date] )
RETURN
    SUMX ( FILTER ( Data, [flaglost] = 1 && [Date] = d ), [Amount] )
    Measurelost = 
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( 'Date'[Date] ),
        "flag",
            VAR lastd =
                CALCULATE (
                    MAX ( Data[Date] ),
                    FILTER ( ALL ( Data ), [Date] < EARLIER ( 'Date'[Date] ) )
                )
            VAR cur =
                CALCULATETABLE (
                    DISTINCT ( Data[Customer] ),
                    FILTER ( ALL ( Data ), [Date] = EARLIER ( 'Date'[Date] ) )
                )
            VAR last =
                CALCULATETABLE (
                    DISTINCT ( Data[Customer] ),
                    FILTER ( ALL ( Data ), [Date] = lastd )
                )
            RETURN
                COUNTROWS ( EXCEPT ( last, cur ) )
    )
RETURN
    SUMX ( tab, [flag] )

     

    Note: Don't create relationships in dates or you can create a single distinct table as slicer.

    If you still need help, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, svinayagam1984 

 

Has your problem been solved? I saw it has results in your sample file. What's wrong?

You can try: 'CALENDAR(MIN(Data[Date]),MAX(Data[Date]))' to create a date table quickly. 

If it doesn’t solve your problem, Can you share the logic you want to calculate and the expected result and feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • svinayagam1984's avatar
    svinayagam1984
    Icon for Helper I rankHelper I
    5 years ago

    Thanks, v-janeyg-msft for the follow-up. 
    I didn't get a solution to resolve my issue.

    Thanks, for the follow-up.
    I didn't get a solution to resolve my issue.

    As you can see in my Sample file, Both New (New # & New $) and
    Cancellation ( Lost # & Lost $)measures are working perfectly if I select a single date from the slicer.

    But when I select multiple dates from the slicer, only New measures give me the aggregated results. I need your help in fixing the cancellation measure to see the aggregated result.

    I want to see $-400 in cancellation as you see in my New.

    FYI - I am trying to compare the weekly (Mon-Sat) data.

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, svinayagam1984 

       

      Can you tell me the logic of how to get the data of Lost when multiple selections are made? Your measure only calculates the smallest one and It's actually a bit messy. 

       

      Best Regards

      Janey Guo

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • svinayagam1984's avatar
        svinayagam1984
        Icon for Helper I rankHelper I
        5 years ago

        Thanks for your time v-janeyg-msft 
        I am trying to show New and churn in a table visual like below,

        New - Customers don't exist in the previous week but, available in the current week

        Lost - Customers exist in the previous week but, not available in the current week

         

        As you can see in the above image, the New # and New $ are displaying correct results, and the total also correct. But Lost # and Lost $ are displaying correct results in individual weeks but the total is wrong.

        Because of this issue, I am not able to display all the seven accounts that are churned during these 3 weeks. I am getting only 3 on the list.