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.
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.
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,
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.
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.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.