MAX Date based on selected Fact measure (Direct Query)

DAX DAX Calculations
1

Hello,

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)

Thanks,
Kevin

2

Thanks for posting your question @kkieger To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; 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.

Including all of the above will likely enable a quick solution to your question.

3

Hi @kkieger,

Have you tried a simple construct like this, to get the last date from your fact table?

MAXX( ALL(Sales), Sales[Bill Date])

4

Thank you, Melissa.

I can render results based on the Calendar dim, but I do not have a Sales fact table [with a Bill Date] in this cube. Please use the attachment to illustrate what is happening, and provide further info/explanations.

MAX Date in Direct Query.docx (186.0 KB)

Kevin

5

Melissa,

I have an updated document which adds (see ‘Additional Example’) what I think is a better example in working with this cube of aggregated measures. It gets past some issues with Direct Query and DAX.

MAX Date in Direct Query.docx (364.0 KB)

Kevin

6

I just read that when working with multidimensional models, Power BI applies rules to how it processes data, based on which column is defined as the default member . Since you referenced a hierarchy is that something you’ve looked into?

.
Q: Since you have measures in the Cube. Isn’t there a way to add last sale date directly to the Cube?

Never having worked with a Cube myself, I’m afraid I don’t understand why something like this wouldn’t work… I’m sorry I can’t be of more assistance maybe another member can help you out.

CALCULATE(
    MAX( Calendar._Calendar Hierarchy.Bill Date ),
    FILTER( ALL( Calendar ), [Billed Sales] > 0 )
)
7

Hi @kkieger, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

8

Hi Melissa,

To confirm, the measure you provided, amongst several others, do not work. We feel it has to be resolved in the context of the AtScale cube connector we’re using in Power BI Desktop.

I will mark the topic as closed.

Kevin