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

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.