cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
basroozen Member
Member

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 = 😎
  • 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

1 ACCEPTED SOLUTION

Accepted Solutions
basroozen Member
Member

Re: Filtered Result to date with MoM overview

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

 

View solution in original post

15 REPLIES 15
LivioLanzo Super Contributor
Super Contributor

Re: Filtered Result to date with MoM overview

Hllo @basroozen

 

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> ) )

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: Filtered Result to date with MoM overview

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

basroozen Member
Member

Re: Filtered Result to date with MoM overview

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 Super Contributor
Super Contributor

Re: Filtered Result to date with MoM overview

Hi @basroozen

 

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] ) )

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

basroozen Member
Member

Re: Filtered Result to date with MoM overview

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?

basroozen Member
Member

Re: Filtered Result to date with MoM overview

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

LivioLanzo Super Contributor
Super Contributor

Re: Filtered Result to date with MoM overview

hI @basroozen

 

that's right. within Calculate we cannot do that. thats what i get for not testing the measures Smiley Very Happy

 

try

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

basroozen Member
Member

Re: Filtered Result to date with MoM overview

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 Super Contributor
Super Contributor

Re: Filtered Result to date with MoM overview

Hi @basroozen

 

is the 'Date' table marked as date table? 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)