Use last known value if no data in show items with no data.

[awff]

Happy new years!

The below visual is simply the number of users in a period with the line showing the total licenses at the time. This visual has "Show items with no data" turned on because we also want to see days where there were no sessions.

My request is that I want the Total licenses to be continuous and use the last value even if no data exists until the next update. So ideally, there should be no breaks in the line graph where there are no check-outs. I've tried LASTNONBLANKVALUE with no luck unfortunately!

Total licenses is a measure as below:

TotalLicenses = 
CALCULATE(
    SUM('2_ProductLog_df'[TotalLicenses]),
    FILTER(
        '2_ProductLog_df',
        '2_ProductLog_df'[CompanyAbbrev] = '1_SessionLogs_py'[CompanyAbbrev] &&
        '2_ProductLog_df'[SessionStart] <= '1_SessionLogs_py'[SessionStart] &&
        '2_ProductLog_df'[SessionNextUpdate] >= '1_SessionLogs_py'[SessionStart]
    )
)

Any tips is appreciated! 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[awff]

Here is a simplified sample data:

Sessions:

SessionStart	TotalLicenses
9-Jan-23	4
9-Jan-23	4
9-Jan-23	4
10-Jan-23	4
11-Jan-23	6
11-Jan-23	6
11-Jan-23	6
11-Jan-23	6
11-Jan-23	6
12-Jan-23	6
12-Jan-23	6
16-Jan-23	4
20-Jan-23	5
29-Jan-23	9
31-Jan-23	5
5-Feb-23	5
20-Feb-23	9
4-May-23	4

The desired result is where the TotalLicenses will use the previous non-blank value for dates with no data in the session table. If this could be done in a measure, it would be great as the dataset is quite large...

Date	Desired Result
1-Jan-23
2-Jan-23
3-Jan-23
4-Jan-23
5-Jan-23
6-Jan-23
7-Jan-23
8-Jan-23
9-Jan-23	4
10-Jan-23	4
11-Jan-23	6
12-Jan-23	6
13-Jan-23	6
14-Jan-23	6
15-Jan-23	6
16-Jan-23	4
17-Jan-23	4
18-Jan-23	4
19-Jan-23	4
20-Jan-23	5
21-Jan-23	5
22-Jan-23	5
23-Jan-23	5
24-Jan-23	5
25-Jan-23	5
26-Jan-23	5
27-Jan-23	5
28-Jan-23	5
29-Jan-23	9
30-Jan-23	9
31-Jan-23	5

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ritaf1983]

Hi @awff 

Please refer to the linked discussion :

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Fill-in-blanks-with-last-non-blank-v...

If it doesn't help

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[awff]

Thanks,

The solution in the post looks promising, but didn't quite resolve the issue, unfortunately. My data does not have a continuous date range for each session.

Here is a link to a sample dataset

https://www.icloud.com/iclouddrive/0a6GBQSFt2BfWgj022bYjCPsA#sampleData