Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtered Result to date with MoM overview

Hi, I hope you can help me with this one as I cannot get my head around it. I have a table that sort of looks like this:   User ID Is making use of our system System Sign up date Orde...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi LivioLanzo,

    I found the solution thanks to your help, I think I was making things a bit too complex. Eventually the trick was to just use DATESBETWEEN, and set the start date to a very early date:

     

    # Outlets signed up =

CALCULATE(

distinctCOUNT('Contacts Master'[ID Outlet]),
'Contacts Master'[Signed Up Outlet] = true(),
DATESBETWEEN(_Date[Date], DATE(1901,1,1), LASTDATE(_Date[Date]) )

)

    Thanks for your help on getting me there!

    Cheers

    Bas

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi LivioLanzo,

This results in the following measure:

 

# Outlets signed up =
CALCULATE(
distinctCOUNT('Contacts Master'[ID Outlet]),
'Salesforce - Contacts Master'[Signed Up Outlet] = true(),
_Date[Date] <= MAX(_Date[Date])
)

 

_Date is my date table.

 

I then receive the message that 'a function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.' Would you know how to fix that?

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

hI Anonymous

 

that's right. within Calculate we cannot do that. thats what i get for not testing the measures :smileyvery-happy:

 

try

CALCULATE( <your measure}>, FILTER( ALL( Calendar[Date] ), Calendar[Date]  <= MAX( Calendar[Date] ) ) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello LivioLanzo


    The formula works fine. Unfortunately the numbers now don’t match anymore, sorry to be a pain!

    With the previous DATESYTD formula we landed on these numbers, and that worked well as these are all correct.

     

    Month

    Signed up users

    MoM %

    Sept

    365

    3956%

    Oct

    1182

    224%

    Nov

    1431

    21%

     

    With your latest formula we land on these numbers:

     

    Month

    Signed up users

    MoM %

    Sept

    1388

    0%

    Oct

    1424

    3%

    Nov

    1435

    1%

     

     

    This is the formula I've used based on your input

    # Outlets signed up =

    CALCULATE(

    distinctCOUNT('Contacts Master'[ID Outlet]),

    'Contacts Master'[Signed Up Outlet] = true(),

    FILTER( ALL( _Date[Date] ), _Date[Date] <= MAX( _Date[Date] )

    )

     

    Any last ideas?

     

    Regards

    Bas

    • LivioLanzo's avatar
      LivioLanzo
      Icon for Solution Sage rankSolution Sage
      7 years ago

      Hi Anonymous

       

      is the 'Date' table marked as date table? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        LivioLanzo yes it is. The first formula you came up with also worked fine with this date table. 

    • LivioLanzo's avatar
      LivioLanzo
      Icon for Solution Sage rankSolution Sage
      7 years ago

      The % looks correct in your picture. I am guessing the signed up users looks wrong to you?

       

      are you able to share a file?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi LivioLanzo,

        Unfortunately, I’m not allowed to share the file. Yes the MoM% gets processed correctly, but the totals are just not right. And apart from this formula I haven’t changed anything in the report.

         

        Would you know any other way of approaching this?

         

        Regards

        Bas