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

Orders placed

AAAA11

TRUE

Jan 1 2017

1

BBBB22

TRUE

Feb 1 2017

2

CCCC33

FALSE

 

 

DDDD44

FALSE

 

 

EEEE55

TRUE

Mar 1 2017

6

FFFF66

TRUE

Apr 1 2017

7

GGGG77

TRUE

May 1 2017

8

HHHH88

TRUE

Jun 1 2017

9

 

I’ve created various measures to calculate:

  • The amount of users in my database (distintcount of user id = 8)
  • The amount of users that are making use of my system (# users where making use of system is true = 6)
  • The amount of users that signed up for my system and placed more than 3 orders (#users where orders placed > 3 = 4)
  • The % of users that signed up for my system and placed more than 3 orders (4/6 = 66%)

 

I’ve put these measures in visualisations and added a filter on top of that to filter the results by month, as I want a monthly report of all of these measures. That report should also show the Month Over Month Change for all measures.

 

What happens when I filter the data to see the results for the month of June (and to see the MoM results compared to May), is that it only returns me the amount of users for June, which is 1 in this example.

I’m looking for a formula that makes it possible to see the total amount of users up to June if the filter is set to June (=8), or the total amount of users for March (=3) if the filter is set to March. I think I need to use the ALL function but I’m not sure how to use it.

 

Thanks in advance

Bas

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

     

15 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution 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
        Solution 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

      Greg_Deckler Looks great, I'll have a look at the intelligence behind your sheet as well, thanks!