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

     

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

Hi Anonymous

 

it performs a year to date aggregation therefore it is reset at the beginning of each year. If you want a sort of a sort of Very First Day to Date aggregation, we need to modifiy the filter argument in calculate.

 

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

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?