Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
We have revenue posted in months based on invoice date, but we would like to know what revenue should have been posted in previous periods based on service dates. For example, if the invoicing period is June, I would need to calculate the revenue from all invoices in June that have Service Dates prior to June.
I know the below DAX does not work, but it highlights what I am trying to do:
USD Inv - Rollover =
CALCULATE(
[USD Invoiced],
ALLSELECTED( Dates ),
USERELATIONSHIP( Invoices[Date Service], Dates[Date] ),
DATESBETWEEN(
Dates[Date],
MIN( Invoices[Date Service] ),
MIN( Invoices[Date Invoice] )
)
)
Here is a screen shot showing what I would like to calculate:
Any help would be greatly appreciated. Thanks.
Solved! Go to Solution.
I figured it out:
USD Inv - Rollover =
VAR MinInv =
CALCULATE (
MIN ( Invoices[Date Invoice] ),
)
VAR Prev =
FILTER (
VALUES ( Invoices[Date Service] ),
Invoices[Date Service] < MinInv
&& NOT( ISBLANK ( Invoices[Date Service] ) )
)
VAR Result =
CALCULATE (
[USD Invoiced],
( Prev )
)
RETURN
Result
Hello @JRHans09
If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.
You can send the sample .pbix file by adding it to your drive or dropbox and add the link here.
Regards
Kumail Raza
I figured it out:
USD Inv - Rollover =
VAR MinInv =
CALCULATE (
MIN ( Invoices[Date Invoice] ),
)
VAR Prev =
FILTER (
VALUES ( Invoices[Date Service] ),
Invoices[Date Service] < MinInv
&& NOT( ISBLANK ( Invoices[Date Service] ) )
)
VAR Result =
CALCULATE (
[USD Invoiced],
( Prev )
)
RETURN
Result
Hi,
Cannot understand anything from such a small image. Share the download link of your file with your notes in there (as seen in the screenshot) and show the expected result there.
Clicking to expand the image should provide sufficient resolution to view the screen shot.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |