Measure based on 2 date column

[Anonymous]

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!

[Fowmy]

@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

[Anonymous]

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. 

[Fowmy]

@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

[Anonymous]

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....

[Fowmy]

@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

[mahoneypat · Accepted solution]

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