POWER BI / DAX : problem creating a filtered table using an existing measure

Question

0.00/5 (No votes)

See more:

I want to calculate total sales for all dates in a table where the date is less that a specific date. The specific date is stored in another measure.

What I have tried:

TotSales = 

/* Does not work if filtering using a pre-existing measure called [EndOfWeekDate] */
/*var FilteredTable = FILTER('Sales', 'Sales'[CYDate] < [EndOfWeekDate])*/

/* But it works if I hard code a date */
var FilteredTable = FILTER('Sales', 'Sales'[CYDate] < DATE(2024,17,7))

RETURN 

IF(
    
    SUM('Sales'[Sales]) = 0, 

    BLANK(),

    CALCULATE (SUM('Sales'[Sales]),  FilteredTable)

)

Posted 18-Apr-24 0:48am

AndyW1969

Add a Solution

Comments

M-Badger 18-Apr-24 7:02am

In the hardcoded version the month and the day seem to be reversed, it's DATE(Year, Month, Day) - hence DATE(2024,17,7) is the 7th day of the 17th month of the year 2024.
What is the format of [EndOfWeekDate] ?

AndyW1969 18-Apr-24 7:10am

thanks for your reply. Here's the measure syntax. I can display this measure in a card visual and it shows the correct date value.

EndOfWeekDate = CALCULATE(max('Calendar'[Date]),'Calendar'[IsWeeklyReportWeek]=TRUE())

M-Badger 18-Apr-24 8:39am

What does that expression evaluate as ? Does it return a value that PowerBI recognises as a date ?

AndyW1969 18-Apr-24 9:22am

Yes it evaluates to a date of the format dd/mm/yyyy, with a value of 13/04/2024

M-Badger 18-Apr-24 9:24am

Does DATE(2024/04/13) evaluate to the same thing ?

AndyW1969 18-Apr-24 10:15am

Yes that works fine. So you would expect the measure to, also. Very strange. Btw thanks for your responses on this.

M-Badger 18-Apr-24 10:28am

Does the hardcoded version still work if you correct the order of the month and the day?, i.e. DATE(2024/7/17) ?

M-Badger 18-Apr-24 10:29am

What does "does not work" mean ? Do you get no data or not the data you expect ?
What does "it works" mean ? Do you get the data you expect ?

AndyW1969 18-Apr-24 10:37am

thanks again. "Does not work" meant that no data was returned. I have found a workaround - I now store the date in question to a sql table. This value is updated by a stored procedure that runs daily. I've changed the measure so that instead of calculating the date, it picks it up directly from the sql table. New measure:

EndOfWeekDate =

/*CALCULATE(max('Calendar'[Date]),'JOJO Calendar'[IsWeeklyReportWeek]=TRUE())*/

CALCULATE (MAX('Settings'[ValueDate]), 'Settings'[SettingName]="MaxDate")

thanks again.

M-Badger 18-Apr-24 15:42pm

Glad you got it working

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)