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

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

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,

    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,

    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

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

    Hi Anonymous

     

    one thing i noticed is that on rows of the matix you're just using the month name, therefore how does the matrix know at which year to look for September?  Consider that now you're getting a cumulative sum of all the days up until end of September as well. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI LivioLanzo,

    In my calendar table I have a month and a year column. The date column itself is linked with the user subscription date of my customer table. The calendar table is marked as the date table. 

     

    In the example I sent you indeed I forgot to add the year. There is a year filter on this page as well, and it is ticked. This is also why the previous formula with DATESYTD works perfect. I just can't get my head around why the last formula you sent generates different results, as it all should be fine. I've also sorted my subscription dates ascending to check wheter that would make any difference, but no luck so far!


    Thanks

    Bas