cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jc4
Frequent Visitor

compare column date to measure date in calculated column

I'm trying to compare a measure that returns a date to a date column. 

 

The measure finds the date selected on a slicer:

 

Date - Minimum Selected = 
CALCULATE(
MIN('Demand Archive'[CREDAT_0]), ALLSELECTED( 'Demand Archive')
)

 

 

The calculated column then compares the measure to the column and returns a 0 if they dont match:

LBS First = 
IF(DATEVALUE([Date - Minimum Selected]) = 
DATEVALUE('Demand Archive'[CREDAT_0]), 
'Demand Archive'[LBS - Ordered], 0) 

When I put everything in a table, the calculated column displays a 0 regardless of the date values. I'd like the highlighted row below to display 1,320:

 

Capture.PNG

 

I'm working with one table that tracks contract volumes over time. The sample data is as follows:

SO #SO LineProductLBS - OrderedCREDAT_0
A1000Widget13204/9/2018 0:00
A1000Widget 13204/10/2018 0:00
A1000Widget13204/11/2018 0:00

 

 

 

 

I'm fairly new to the concept of measures vs calculated columns so I could be doing something totally wrong but its also possible that I have a simple syntax mistake in my date comparison. 

 

Any help would be much appreciated!

 

 

 

 

1 ACCEPTED SOLUTION

OK, here is what I would do.

 

I would create two slicer tables from your original table. Just copy and paste your query and then remove all of the other columns other than CREDAT_0. So, you should end up with two tables with just that column in them and those you will use for your slicers. Do NOT create relationships between these tables and each other or to your main fact table. I called these tables "Demand Archive Slicer First" and "Demand Archive Slicer Last"

 

Now, create these measures:

LBS - First Date = 
CALCULATE(
MIN('Demand Archive Slicer First'[CREDAT_0]), ALLSELECTED( 'Demand Archive Slicer First')
)

LBS - Last Date = 
CALCULATE(
MIN('Demand Archive Slicer Last'[CREDAT_0]), ALLSELECTED( 'Demand Archive Slicer Last')
)

LBS First = LOOKUPVALUE('Demand Archive'[LBS - Ordered],'Demand Archive'[CREDAT_0],[LBS - First Date])

LBS Last = LOOKUPVALUE('Demand Archive'[LBS - Ordered],'Demand Archive'[CREDAT_0],[LBS - Last Date])

LBS - Change = [LBS Last] - [LBS First]

Basically, the first two measures are just getting your selected values from your slicers. The next two are looking up the LBS - Ordered that corresponds with each of those dates. Then the last one subtracts the two values.

 

Now you can put Customer, LBS First, LBS Last and LBS - Change into a Table visualization and you should have what you want.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

Sample data to recreate please.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
jc4
Frequent Visitor

Sorry about that, I've posted sample data in my original post. 

I realized the issue you are having. The calculated column approach will not work because that is calculated once at data load, not interactively as something is selected from the slicer. So, that will never work.

 

So, what you want to do is to change it to a measure with this formula:

 

LBS First = 
IF(DATEVALUE([Date - Minimum Selected]) = 
DATEVALUE(MAX('Demand Archive'[CREDAT_0])), 
MAX('Demand Archive'[LBS - Ordered]), 0)

If you place that into a Table visualization with "SO", "SO Line", "Product", "CREDAT_0", "Date - Minimum Selected" and your new measure "LBS First" then you should get what you want. I did.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
jc4
Frequent Visitor

Thank you for your help and that does solve the problem but I probably should have explained my overall goal.

 

I'm trying to create a field that will calculate the difference between "LBS -Ordered" on the first date and last date selected on the slicer. Originally, i was planning on having "LBS First" and "LBS Last" as calculated columns and finding the difference. At that point I could display the difference summarized on different values like customer. I understand that wont work now since calculated columns aren't calculated dynamically as report filters change.

 

Given a new data sample below, how would I go about doing this?

 

SO #SO LineProductLBS - OrderedCustomerCREDAT_0
ABC1000123500XYZ4/9/2018 0:00
ABC10001231500XYZ4/10/2018 0:00
ABC10001231500XYZ4/11/2018 0:00

 

 

The desired result if I was to display change by customer in a table visual would be as follows:

 

Customer LBS - FirstLBS - LastLBS - Change
XYZ50015001000

 

 

I really appreciate the help. I've already learned a lot from this post today.

 

OK, here is what I would do.

 

I would create two slicer tables from your original table. Just copy and paste your query and then remove all of the other columns other than CREDAT_0. So, you should end up with two tables with just that column in them and those you will use for your slicers. Do NOT create relationships between these tables and each other or to your main fact table. I called these tables "Demand Archive Slicer First" and "Demand Archive Slicer Last"

 

Now, create these measures:

LBS - First Date = 
CALCULATE(
MIN('Demand Archive Slicer First'[CREDAT_0]), ALLSELECTED( 'Demand Archive Slicer First')
)

LBS - Last Date = 
CALCULATE(
MIN('Demand Archive Slicer Last'[CREDAT_0]), ALLSELECTED( 'Demand Archive Slicer Last')
)

LBS First = LOOKUPVALUE('Demand Archive'[LBS - Ordered],'Demand Archive'[CREDAT_0],[LBS - First Date])

LBS Last = LOOKUPVALUE('Demand Archive'[LBS - Ordered],'Demand Archive'[CREDAT_0],[LBS - Last Date])

LBS - Change = [LBS Last] - [LBS First]

Basically, the first two measures are just getting your selected values from your slicers. The next two are looking up the LBS - Ordered that corresponds with each of those dates. Then the last one subtracts the two values.

 

Now you can put Customer, LBS First, LBS Last and LBS - Change into a Table visualization and you should have what you want.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
jc4
Frequent Visitor

This is a very interesting approach. I'll give it a shot!

 

Would I easily beable to display the same data but grouped bya field other than customer? Perhaps SO #?

Absolutely. That method should work for every way you might want to slice and dice it after that. The key here is to keep the measures as generic as possible and let their context be determined by the visualization. The LOOKUPVALUE measures you might want to redo potentially depending on your data to something more robust but not sure if that is necessary with your data or not.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
jc4
Frequent Visitor

I'm receiving the error "Calculation error in measure 'Demand Archive'[LBS First]: A table of multiple values was supplied where a single value was expected."

 

I'm guessing this because there are duplicate CREDAT in Demand Archive? 

 

Would this work?

 

SUMX(FILTER('Demand Archive', 'Demand Archive'[CREDAT_0] = [LBS - Last Date]),'Demand Archive'[LBS - Ordered])

 

 EDIT: The measure above appears to be working at this point. Thanks for all the help

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!