cancel
Showing results for
Did you mean:
Highlighted
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.

Bas

1 ACCEPTED SOLUTION

Accepted Solutions
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

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

Proud to be a Datanaut!

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

Proud to be a Datanaut!

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

Super Contributor

## Re: Filtered Result to date with MoM overview

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

Proud to be a Datanaut!

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?

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!

Super Contributor

## Re: Filtered Result to date with MoM overview

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

try

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

Proud to be a Datanaut!

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

Super Contributor

## Re: Filtered Result to date with MoM overview

is the 'Date' table marked as date table?

Proud to be a Datanaut!

Announcements