I’m continuing to work on a new model and report using what I’ve learned from Sam and Enterprise DNA. My current issue probably has something to do with Dates and the inactive relationships that are in place. Attached is a masked model to work with (Thanks @Brian Julius for the video showing how to do this!!). And here are the questions associated with the visual on the Sales 365 report page.
If the date filter is set on the page, the Sales Year to Date measure displays a number. But if you clear the filter, it goes back to null. What is wrong with this formula?
Sales Year to Date =
CALCULATE(
[Total Sales] ,
DATESYTD( Dates[Date] ) )
We want to display sales from the past 365 days. I’ve tried 2 measures for this and they show 2 different answers. Neither of them are correct. Sales from 2 former salespersons appear in this visual, and neither of them should be showing up. Their last sales were more than 365 days in the past.
Hi @npeterson, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
@npeterson
I am nota DAX furu but I just looked at this. The Sales Year to Date measure needs a date context. At the moment when there is no filter on the visual or the page.
When you add a filter to the visual using the date dimension this allows the YTD calc to work.
I normally have a current month ( 0 or 1 ) in my date dimension, current year and maybe current day.
Then when you build a report you can either ignore the date dimension and have a measure for
Sales Year to date and calculate it based on DateYTD of the date of today
I also think to use the Billing.Ship_Date : Dates.Date relationship you will need USERELATIONSHIP as that relationship is inactive.
@ells
The date relationship was established in the Total Sales measure that is used in all of these measures. Wouldn’t that be redundant to reference it again?
Try below formula. In earlier measure, due to Evaluation context Billing table was getting filtered for individual Rep and while calculating max(Billing[Ship_Date]) it was returning dates for individual Rep.
Sales 365 Days =
var MaxSHipDate = CALCULATE(max(Billing[Ship_Date]),all(‘Rep Names’))
return
CALCULATE(
[Total Sales],
DATESINPERIOD( Dates[Date],
MaxSHipDate, - 365, DAY)
)
B/w Rep and Billing Table, there are two table “Order and Order_Details”. The relationship b/w these are based on field SO_Nbr. There are more SO_Nbr in details table than Order table such as 635880.
Similarly b/w Order Details and Billing tables there are lot’s of ID’s present in Billing that are not available in Order Details table like “738282*1”.
As Total Sales are coming from Billing all these missing ID’s are showing in combined as Blank in visual.
Thanks @ankit. The measure you provided achieved what I was trying to do. The errors that were in the tables were the result of stripping out data for masking purposes. I’ll remedy this before using this masked model for future questions.
