Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

LivioLanzo
Solution Sage
Solution Sage

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

 

 


 


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


Proud to be a Datanaut!  

Anonymous
Not applicable

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

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

 


 


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


Proud to be a Datanaut!  

Anonymous
Not applicable

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?

hI @Anonymous

 

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!  

Anonymous
Not applicable

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

The % looks correct in your picture. I am guessing the signed up users looks wrong to you?

 

are you able to share a file?

 


 


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


Proud to be a Datanaut!  

Anonymous
Not applicable

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

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. 

 


 


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


Proud to be a Datanaut!  

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Hi @Anonymous

 

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!  

Anonymous
Not applicable

@LivioLanzo yes it is. The first formula you came up with also worked fine with this date table. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.