topic Re: Measure based on 2 date column in DAX Commands and Tips

Measure based on 2 date column

Anonymous — Mon, 24 Aug 2020 14:49:16 GMT

Hi everyone,

I'm trying to create a measure with 2 date column but with a little twist..

Sold Date	Status	Cancelation Date	Price
Jan	Sold		$10
Jan	Canceled	Feb	$10
Feb	Sold		$10

The ''Sold Date'' is linked to a date fact table. If you select January the total amount should be $20 (based on Sold Date) and if you select February, because there is a cancelation date, the amount need to be substracted from this month. Total for February should be $0. Any idea how to create a measure like that?

Thank you for your help!

Re: Measure based on 2 date column

Fowmy — Mon, 24 Aug 2020 14:24:35 GMT

Anonymous

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Re: Measure based on 2 date column

Anonymous — Mon, 24 Aug 2020 15:10:48 GMT

Thank you Fowmy Here's the link: https://drive.google.com/file/d/1thQdeZO9yuEnW5U4EOzGdlym9RIK57r0/view?usp=sharing

Based on the Date table, if you select January the total should be $20 (based on Sold Date column). If you choose february the total should be $0 ($10 sold in Feb - $10 from the cancelation of February). If you don't select any month, the total should be $10.

Re: Measure based on 2 date column

Fowmy — Mon, 24 Aug 2020 16:20:17 GMT

Anonymous

Try this measure:

Measure = VAR D = FORMAT(MAX('Date'[Date]),"YYYYMM") VAR _CANCELLED = SUMX( FILTER( ALL(Sold), FORMAT(Sold[Cancelation date],"YYYYMM") = D ), [_SUM] ) RETURN IF( HASONEVALUE('Date'[Date].[Mois]), [_SUM] - _CANCELLED, SUMX( ALL(Sold), IF( Sold[Status] = "Sold", Sold[Price] , Sold[Price] * -1) ) )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Re: Measure based on 2 date column

Anonymous — Mon, 24 Aug 2020 18:07:25 GMT

Thank you Fowmy for your time.

I'm not sure how the [_SUM] should work... it's looking for a column called _SUM but there's none....

Re: Measure based on 2 date column

mahoneypat — Mon, 24 Aug 2020 18:27:29 GMT

Please try this measure in a table visual with your Sold Date column. Replace Sold with your actual table name throughout.

Total Sold =
VAR thismonth =
SELECTEDVALUE ( Sold[Sold Date] )
VAR sales =
CALCULATE ( SUM ( Sold[Price] ), Sold[Status] = "Sold" )
VAR cancels =
CALCULATE (
SUM ( Sold[Price] ),
Sold[Status] = "Canceled",
ALL ( Sold ),
Sold[Cancelation Date] = thismonth
)
RETURN
sales - cancels

Also, to get the right total, make the measure above and then use this measure in the visual.

Sales Right Total = SUMX(VALUES(Sold[Sold Date]), [Total Sold])

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Measure based on 2 date column

Fowmy — Mon, 24 Aug 2020 19:01:29 GMT

Anonymous

I forgot to include that measure:

You can download the file: HERE

_SUM = SUM(Sold[Price])

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn