My source is a Direct Query cube. Thus, I believe I am largely limited to new measures and also face significant impacts to the DAX needed for time-based measures. Of course, the ability to provide a PBIX file of Direct Query tables is challenging at best. Thus, I’m attaching a document which shows the basic measures and Calendar table available in the source cube… along with a table visualization to show column/measure relationships.
In the document, I attempted to create a “Refresh Date” measure to represent the Max or most recent Bill Date where Billed Sales > 0. Only its syntax is valid, but it will not render results (visualization error).
It is then with that “Max Billed Sales” date (or Refresh Date) measure that we would want to create the following DAX measures for Billed Sales:
Month to Date (e.g. July 1 - 17, 2020)
Year to Date (e.g. January 1 - July 17, 2020)
Previous Month to Date (e.g. June 1 - 17, 2020)
Previous Year to Date (e.g. January 1 - July 17, 2019)
For what it is worth, I’ve reviewed this DNA video, but I can’t apply it successfully to these Direct Query fact measures and Calendar dim table. I am hoping you can use the info I’m providing in the attached to help me produce some of these measures which the Direct Query cube does not.
MAX Date in Direct Query.docx (129.5 KB)