Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |