Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
I think the file attached has what you wanted. However, please check it thoroughly because it does work according to your description but the output is not always obvious. First, to understand it, please filter the slicer by Product 1 and take a good look at the tables in the report.
Best
D
@Anonymous Thanks for your quick and professional reply!
my sales data is monthly, therefore [Date] is always the 1st day for each month.
i focused on your [# Distinct Custs That Bought (2MR)] measure and it seems pretty complex for me and regarding my data.
if i understood correctly, you built some date variables inside the above measure DAX.
i wonder if all these are neccesary due to my "simple" dates values as i mentioned above.
in addition, to correct myself from my original post - i dont have any calendar table because i found it useless as i have date values only on SALES database, so i can use these date values.
if i am right, can you help me with "ligthening" your DAX so it will better fit my project?
thanks in advance,
i appreciate your time,
Amir
# Distinct Custs That Bought (2MR) =
// This var gets the very latest
// date from the Sales fact table.
var __latestDateInSales =
CALCULATETABLE(
LASTDATE( Sales[Date] ),
ALL( Sales )
)
// This var gets the latest month
// in which a purchase occured.
// The month identifier must be
// unique across the whole calendar,
// hence I use YearMonthOrder.
var __latestMonthWithSales =
CALCULATETABLE(
VALUES( 'Calendar'[YearMonthOrder] ),
'Calendar'[Date] = __latestDateInSales,
ALL( 'Calendar' )
)
// This var gets the very last date
// visible in the calendar in the current
// context.
var __lastDayOfMonth = MAX( 'Calendar'[Date] )
// This variable calculates the period
// of 2 last months starting with the
// currently visible month.
var __periodToCalculateOver =
DATESINPERIOD(
'Calendar'[Date],
__lastDayOfMonth,
-2, MONTH
)
// This var just gets you the measure
// over the 2-month period.
var __result =
CALCULATE(
[# Distinct Cust That Bought],
__periodToCalculateOver
)
// This var is TRUE if the visible period
// of time in the current context is just
// one month.
var __oneFullMonthVisible = HASONEVALUE( 'Calendar'[Year Month] )
// This var makes sure that the curently
// visible month is not after
// the last month in the Sales fact table.
// If it were, it would mean we're trying
// to calculate for the future.
var __periodFullyInThePast =
MAX( 'Calendar'[YearMonthOrder] ) <= __latestMonthWithSales
// This var is TRUE if the period we're calculating
// in is not in the future and only one month
// is visible in the current context.
var __shouldCalc =
__periodFullyInThePast
&& __oneFullMonthVisible
return
// We return the value if all the
// above relevant conditions are met.
if( __shouldCalc, __result )
This is the kind of calculation you have to do when you want to have a well-behaved measure that does not show values for the future. The correct calendar in a model is a MUST and it has to cover all full years.
Best
D
Hi @Anonymous
thanks again for your assistance.
i implemented all your measures including calendar in my project and still do not get the desired solution.
below attached some snapshots from my tables (exactly same features as you did) and from my data relationships so you can "feel" the data i work with - sorry for not publishing my PBI project. i guess you can understand the sensitivity.
i hope you will manage to further help me out with what you now have.
please consider that eventually i deal with bunch of customers ([cluster]) and need to be able to show the numbers of customers bought product ([SKU]) "x" among all active customers in the same group. this will represent me the availability rate of a group (cluster) for each product (sku) seperately.
for example "cluster 111" has 100 active customers (from CUSTOMERS dataset) and in the last 2 months only 50 customers among the 100 have bought "sku x". therefore, the availability rate of "cluster 111" for "sku x" is 50%. 3 months ago this rate could have been 30%...
hope you understand my final destination and purpose of the project.
@Anonymous
yes i did mark it as a date table.
that why i added 2 snapshots so you can see what is wrong:
please notice that the mid table which presents the [# Distinct Custs That Bought (2MR)] shows the same number for each month. i expect it to show different values.
please have a look at the example of 1 customer with a specific product: right table with sales is ok. but still it shows 1 for each month at the left table (2020-Mar & 2020-Apr should show blank according to your project)
Sales of -1? It's more than likely that your data does not adhere to the assumptions that my model works on. Hence you get something you don't expect.
What's a sale of -1? My model works perfectly well and gives correct answers as you've probably seen. If yours does not, assumptions are violated for certain. Well, you did not expose everything important about your model when you were posting this problem...
Best
D
-1 is return of an item by the customer. we should consider only sales above zero as a sale.
i appreciate it if you try to keep helping me, but if not thats ok as well of course.
in my original model i created a measure of:
i dont think there any other nuances regarding my data. overall my project has basic databases as customers list with all their features (including cluster, territory etc.), monthly customers sales of 42 different products, products list and following your comments - calendar table.
i understand your comment regarding the useless FILTER in CALCULATE. can you think of a solution to add this ROLLING 2 MONTHS SALES SUM>0 condition to the distinct count of the ROLLING 2 MONTHS CUSTOMERS and of course related to DATE so each month the 2 above measures will be changed and not be fixed for all months as i have currently.
thanks again
I think the file attached has what you wanted. However, please check it thoroughly because it does work according to your description but the output is not always obvious. First, to understand it, please filter the slicer by Product 1 and take a good look at the tables in the report.
Best
D
Hi again,
@Anonymous
After today's refresh - updated up to yesterday's sales data - i got blank data.
In my opinion that is because refresh time is May while the last sales data available is April 30th - maybe the measures get "corrupted" in this situation.
to approve it i will have to wait for May 4th refresh because first sales data for May will be May 3rd (always 1 day after).
My question is whether you can tell me my assumption is correct or not.
Thanks in advance,
Amit
The formulas are perfectly fine. If you start to study them, you'll notice that they will not show values for future months. How is a future month defined? It's the first month in which there are no sales after the last month with non-zero sales. Since you don't have any sales in May, May is still in the future.
Best
D
@Anonymous
Thanks for your quick reply.
I know the measures will not show values for future months, thats extremely fine.
I just wonder why i get blank values. I would expect to see the latest values of March & April.
can i send you my project privately?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |