Forum Discussion

elkjoperik's avatar
elkjoperik
Helper II
7 years ago
Solved

IF filter value does not exist, then use other values

Hi!

I have a problem I have not found any solutions to. 

 

I've made a dynamic this week vs last week metric;

 

Sales PW =

VAR CurrentWeek = SELECTEDVALUE(Date[Week Nr])
VAR CurrentYear = SELECTEDVALUE(Date[Year])
VAR MaxWeekNumber = CALCULATE(MAX(Date[Week Nr]);ALL(Date))


// If week = 1, then it will calculate against week 52 or 53 fix
RETURN
SUMX(
FILTER(ALL(Date);
IF(CurrentWeek = 1;
Date[Week Nr] = MaxWeekNumber && Date[Year] = CurrentYear - 1;
Date[Week Nr] = CurrentWeek -1 && Date[Year] = CurrentYear) );
[Sum Revenue product])

 

The forumla works great, but, I have a segment for type of revenue:

- Campaign sales

- Email sales

- Non campaign sale 

 

and so on.

 

The problem

- If i select week 24, I have two segments; campaign sales and non campaign sale.  

- I select "campaign sale" to see all the product sales from this segment, and want to see this against last week.

 

Since I've used the segment "campaign sale" and i dint have this in week 23, it says i had no sales. 

 

How can i bypass this so it will calculate all sales from week 23 ? 

- I would like to see what sale I had in week 23 on the difff

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi elkjoperik,

     

    There is a solution. Please refer to the file in the attachment. You need to rebuild the model. It's hard to filter the proper data in the model in which all the data is stored in one table. 

    Solution =
VAR lastweekTotal =
    CALCULATE (
        SUM ( LW_DUMMY[Revenue FY] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[WeekNum]
                = MIN ( 'Calendar'[WeekNum] ) - 1
        )
    )
RETURN
    IF (
        ISBLANK ( lastweekTotal ),
        CALCULATE (
            SUM ( LW_DUMMY[Revenue FY] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[WeekNum]
                    = MIN ( 'Calendar'[WeekNum] ) - 1
            ),
            ALL ( Segments )
        ),
        lastweekTotal
    )

    IF-filter-value-does-not-exist-then-use-other-values

    Best Regards,
    Dale

11 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi elkjoperik,

     

    It's possible. You need to create a new table TypeOfRevenue then establish a relationship with your current table. You can bypass the filter using "ALL". But the details depend on your data and its structure. Can you share a dummy sample, please?

     

    Best Regards,

    Dale

    • elkjoperik's avatar
      elkjoperik
      Helper II
      7 years ago

      Thank you for your reply, sorry for my late reply. 

      What I'm trying to do is:

       

      IF I've selected Segment = email (for example), I would like to find the last week values, without searching for last weeks sales with FILTER = email. 

       

      I want to find out this weeks sales through email (all the SKUs) and benchmark that against last week sales, 

       

      If I havn't got any email sales last week, the current state of the PBI will return 0 in revenue, but, if I somehow make it search through all segments last week, I will get a result.

      Did this clear things up ?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi elkjoperik,

         

        Can you share a dummy sample? Then I can write down the DAX formula.

         

         

        Best Regards,
        Dale