Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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
Solved! Go to Solution.
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
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...
@Greg_Deckler Looks great, I'll have a look at the intelligence behind your sheet as well, thanks!
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!
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!
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
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!
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!
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!
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
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!
@LivioLanzo yes it is. The first formula you came up with also worked fine with this date table.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |