DAX Measure to get the previous days count if there is no data for the current day

[manojsv20]

Hello All,

I have a request to create following Power BI table visual. Have included the input data table we are getting from the source and the input data will have values only for the date if there is any change in the items cleared.

Challege here is to create the table visual with all days even though there is no data available for those days in Input table.  Have used the Date table and join that with the input table. Was successful in creating the Total, Total YTD cleared using TOTALYTD dax function. 

However, I wanted to create a DAX measure for the Inventory column. If there is no data for the corresponding day DAX measure has to refer the previous day counts where it has the data in the input table and show it in Power BI table visual. Please refer the Output Power BI table visual and Input table for more information.

I would really appreciate for any suggestions or input in resolving this issue.

Output - Power BI Table Visual
Date	Total	Total YTD Cleared	Inventory
1/1/2020	5000	0	5000
1/2/2020	5200	200	5000
1/3/2020	5200	500	4900
1/4/2020	5200	500	4900
1/5/2020	5200	500	4900
1/6/2020	5200	500	4900
1/7/2020	5200	500	4900
1/8/2020	5200	500	4900
1/9/2020	5200	500	4900
1/10/2020	5200	800	4700

Input Data
Date	Total	Cleared	Inventory
1/1/2020	5000	0	5000
1/2/2020	5200	200	5000
1/3/2020	5200	300	4900
1/10/2020	5200	500	4700

Thank you!!

[v-alq-msft]

Hi, @manojsv20 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @manojsv20 

Based on my research, you may create measures as follows.

Inventory measure = 
var _date = 
CALCULATE(
            MAX('Input Data'[Date]),
            FILTER(
                ALLSELECTED('Input Data'),
                'Input Data'[Date]<MAX(DateTable[Date])
            )
)
return
IF(
    ISBLANK(MAX('Input Data'[Total])),
    LOOKUPVALUE('Input Data'[Total],'Input Data'[Date],_date)-
    LOOKUPVALUE('Input Data'[Cleared],'Input Data'[Date],_date),
    MAX('Input Data'[Total])-MAX('Input Data'[Cleared])
)

Total YTD Cleared = TOTALYTD(SUM('Input Data'[Cleared]),DateTable[Date])

Total measure = [Inventory measure]-[Total YTD Cleared]

Result:

Best Regards

Allan

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

[sturlaws]

Hi @manojsv20 

you will have to write three measures to achieve this, like this:

MeasureTotal =
VAR _d =
    CALCULATE ( SELECTEDVALUE ( dates[Date] ) )
VAR _lastDateInInput =
    CALCULATE ( MAX ( data[Date] ); ALL ( data ) )
VAR _dMax =
    CALCULATE ( MAX ( data[Date] ); FILTER ( ALL ( data ); data[Date] <= _d ) )
VAR _total =
    IF (
        _d <= _lastDateInInput;
        CALCULATE ( SUM ( data[Total] ); FILTER ( ALL ( dates ); dates[Date] = _dMax ) );
        BLANK ()
    )
RETURN
    _total

I created a sample report based on the data you provided: report

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[manojsv20]

Hi @sturlaws,

Thanks a lot for your response!!

I checked the report you shared. All looks good, but the Measure total value didn't show the correct value. Count expected from 1/3 to 1/9 is 4400, but the report shows 5200. Count for 1/10 has to be 3700.

Would appreciate any suggestions to resolve this issue

Date	MeasureInventory	MeasureCleared	MeasureTotal
1/1/2020 0:00	5000	0	5000
1/2/2020 0:00	5000	200	5200
1/3/2020 0:00	4900	500	5200
1/4/2020 0:00	4900	500	5200
1/5/2020 0:00	4900	500	5200
1/6/2020 0:00	4900	500	5200
1/7/2020 0:00	4900	500	5200
1/8/2020 0:00	4900	500	5200
1/9/2020 0:00	4900	500	5200
1/10/2020 0:00	4700	1000	5200

[sturlaws]

Sorry, thought the Output table you provided was the desired output. Try rewriting the measure to this:

MeasureTotal = [MeasureInventory]-[MeasureCleared]