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

Hllo Anonymous

 

have you created a date dimension linked to your fact table and marked it as a Date Table?

 

then you should be able to do it with

 

= CALCULATE( <your measure>, DATESYTD( calendar<date> ) )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi LivioLanzo,

    Thank you for the quick reply. That works! From what I understand, that function is showing the result to date of the given year. I think that would mean that my total amount of users will be reset to 0 on 1/1/2019 . Is that true? Or will this function always continue to keep summing up the numbers?

     

    Thanks!
    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?