Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
There are two tables in my database: one with transaction date and transaction value, as shown in the table below.
Second, dates table.
For every day in the chosen period (by dates table), I need to show values. If there is no transaction on some day, I should show the last transaction with its value.
I tried using some variations of function LASTNONBLANK without success.
I would appreciate your assistance.
link to PBIX :
PBIX last no blank
Date | sum | |
01/02/2023 | 3 | |
02/02/2023 | 5 | |
05/02/2023 | 6 | |
06/02/2023 | 7 | |
08/02/2023 | 8 |
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Lastnonblank value: =
VAR _currentyear =
MAX ( DateTable[Year] )
VAR _lastnonblankyear =
CALCULATE (
LASTNONBLANK ( DateTable[Year], [totalValue] ),
FILTER ( ALL ( DateTable[Year] ), DateTable[Year] <= _currentyear )
)
RETURN
IF (
HASONEVALUE ( Suppliers[SupplierName] ),
CALCULATE ( [totalValue], DateTable[Year] = _lastnonblankyear )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you for your response.
I updated your code to
Hi,
Please check the below picture and the attached pbix file.
Lastnonblank value: =
VAR _currentyear =
MAX ( DateTable[Year] )
VAR _lastnonblankyear =
CALCULATE (
LASTNONBLANK ( DateTable[Year], [totalValue] ),
FILTER ( ALL ( DateTable[Year] ), DateTable[Year] <= _currentyear )
)
RETURN
IF (
HASONEVALUE ( Suppliers[SupplierName] ),
CALCULATE ( [totalValue], DateTable[Year] = _lastnonblankyear )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you for your response.
I updated your code to
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |