Measure to copy the the values of previous date

[Anonymous]

Hi all,

Related thread- Duplicate the values in a matrix without entering data in the excel sheet

I am currently working on a dashboard. I have a matrix table for companies.  The problem was there were some dates in the data which has value for just one or two companies( coz there are some other columns whose values have changed for that particular company).   I wanted to use the previous date value if the data is not there. I didn't want to add a row in the source excel sheet.

The thread mentioned above gives the m-code and measure that serves the purpose to some extent. If there are no filters in the table it goes well but when I am applying filter on Sites and Region column I am not getting correct result.

The below M-code and Measure helps to have null values if data is not present and use the previous value if not present respectively

Measure-

Full Number = 
VAR varCurrentDate =
    MAX( 'Table'[Date] )
VAR varCurrentCategory =
    MAX( 'Table'[Company] )
VAR varCurrentValue =
    SUM( 'Table'[Rate] )
VAR varPreviousDate =
    MAXX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Rate]
                <> BLANK()
                && 'Table'[Date] < varCurrentDate
                && 'Table'[Company]= varCurrentCategory
        ),
        'Table'[Date]
    )
VAR varPreviousValue =
    SUMX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Date] = varPreviousDate
                && 'Table'[Company] = varCurrentCategory
        ),
        'Table'[Rate]
    )
VAR Result =
    SWITCH(FALSE(), ISBLANK(varCurrentValue),varCurrentValue
    ,ISBLANK(varPreviousValue),varPreviousValue
    )
RETURN
    Result

When we apply filters, it is not copying the previous date value. I tried to use ALLSELECTED and ALLEXCEPT in the measure above but didn't work.

EX- I want that when I select Rural it should populate the data for 26 Feb for all company and same for Urban.

Please note: There is no relationship with any other table.

Sample Data-

Date	Company	Rate	Discount(%)	Standing Rate	Standing  Discount(%)	Sites	Region
03/01/2020	Blackberry	6.016	14	26.56	0	CompanyWeb	Rural
03/01/2020	Eric	6.097	5.5	28.31	0	CompanyWeb	Rural
03/01/2020	Sony	6.540	10	31.66	0	CompanyWeb	Rural
03/01/2020	Ecent	7.090	15	30.39	0	CompanyWeb	Rural
03/01/2020	Power	6.780	30	28.33	0	CompanyWeb	Rural
03/01/2020	Isaac	5.905	16	27.67	0	CompanyWeb	Rural
03/01/2020	Ferrari	7.119	30	30.78	56.4	CompanyWeb	Rural
05/02/2020	Sony	6.540	10	31.66	0	CompanyWeb	Rural
05/02/2020	Power	6.780	30	28.33	0	CompanyWeb	Rural
05/02/2020	Isaac	5.905	16	27.67	0	CompanyWeb	Rural
05/02/2020	Blackberry	6.016	14	26.56	0	CompanyWeb	Rural
05/02/2020	Eric	6.097	5.5	28.31	0	CompanyWeb	Rural
05/02/2020	Ecent	7.090	15	30.39	0	CompanyWeb	Rural
05/02/2020	Ferrari	7.119	20	30.78	38.4	CompanyWeb	Rural
26/02/2020	Ferrari	7.119	28	30.78	56.4	CompanyWeb	Rural
02/03/2020	Ferrari	7.119	28	30.78	56.4	CompanyWeb	Rural
02/03/2020	Sony	6.540	10	31.66	0	CompanyWeb	Rural
02/03/2020	Power	6.780	30	28.33	0	CompanyWeb	Rural
02/03/2020	Eric	6.097	5.5	28.31	0	CompanyWeb	Rural
02/03/2020	Blackberry	6.016	14	26.56	0	CompanyWeb	Rural
02/03/2020	Isaac	5.905	12	27.67	0	CompanyWeb	Rural
02/03/2020	Ecent	7.090	15	30.39	0	CompanyWeb	Rural
01/04/2020	Blackberry	6.016	14	26.56	0	CompanyWeb	Rural
01/04/2020	Sony	6.540	10	31.66	0	CompanyWeb	Rural
01/04/2020	Power	6.780	30	28.33	0	CompanyWeb	Rural
01/04/2020	Isaac	5.905	12	27.67	0	CompanyWeb	Rural
01/04/2020	Eric	5.391	5.5	28.23	0	CompanyWeb	Rural
01/04/2020	Ecent	7.090	15	30.39	0	CompanyWeb	Rural
01/04/2020	Ferrari	7.119	31	30.78	56.6	CompanyWeb	Rural
06/04/2020	Ferrari	7.119	20	30.78	56.5	CompanyWeb	Rural
09/04/2020	Blackberry	6.016	23	26.56	0	CompanyWeb	Rural
21/04/2020	Ferrari	7.119	25	30.78	56.5	CompanyWeb	Rural
03/05/2020	Blackberry	5.618	23	26.56	0	CompanyWeb	Rural
03/05/2020	Eric	5.391	5.5	28.23	0	CompanyWeb	Rural
03/05/2020	Sony	5.79	10	31.66	0	CompanyWeb	Rural
03/05/2020	Ecent	7.090	15	30.39	0	CompanyWeb	Rural
03/05/2020	Ferrari	7.119	25	30.78	56.5	CompanyWeb	Rural
03/05/2020	Power	6.78	30	28.33	0	CompanyWeb	Rural
03/05/2020	Isaac	5.905	12	27.67	0	CompanyWeb	Rural
03/06/2020	Blackberry	5.618	23	26.56	0	CompanyWeb	Rural
03/06/2020	Eric	5.391	5.5	28.23	0	CompanyWeb	Rural
03/06/2020	Sony	5.79	10	31.66	0	CompanyWeb	Rural
03/06/2020	Ecent	6.24	15	30.39	0	CompanyWeb	Rural
03/06/2020	Ferrari	7.119	25	30.78	56.5	CompanyWeb	Rural
03/06/2020	Power	6.78	30	28.33	0	CompanyWeb	Rural
03/06/2020	Isaac	5.905	12	27.67	0	CompanyWeb	Rural
08/06/2020	Blackberry	5.618	23	26.56	0	Direct	Rural
08/06/2020	Eric	5.391	5.5	28.23	0	Direct	Rural
08/06/2020	Sony	5.79	10	31.66	0	Direct	Rural
08/06/2020	Ecent	6.24	15	30.39	0	Direct	Rural
08/06/2020	Ferrari	7.119	25	30.78	56.5	Direct	Rural
08/06/2020	Power	6.78	30	28.33	0	Direct	Rural
08/06/2020	Isaac	5.905	12	27.67	0	Direct	Rural
01/07/2020	Blackberry	5.618	26	26.56	0	CompanyWeb	Rural
01/07/2020	Eric	5.391	5.5	28.23	0	CompanyWeb	Rural
01/07/2020	Sony	5.79	10	31.66	0	CompanyWeb	Rural
01/07/2020	Ecent	6.24	15	30.39	0	CompanyWeb	Rural
01/07/2020	Ferrari	7.119	25	30.78	56.5	CompanyWeb	Rural
01/07/2020	Power	6.78	30	28.33	0	CompanyWeb	Rural
01/07/2020	Isaac	5.905	12	27.67	0	CompanyWeb	Rural
01/07/2020	PPP	6.072	0	30.997	0	CompanyWeb	Rural
03/06/2020	PPP	6.072	0	30.997	0	CompanyWeb	Rural
03/05/2020	PPP	6.072	0	30.997	0	CompanyWeb	Rural
01/04/2020	PPP	6.072	0	30.997	0	CompanyWeb	Rural
02/03/2020	PPP	6.072	0	30.997	0	CompanyWeb	Rural
03/01/2020	PPP	6.072	0	30.997	0	CompanyWeb	Rural
01/07/2020	Blackberry	20.06	32	46.69	0	CompanyWeb	Urban
01/07/2020	Eric	19.22	5.5	45.82	0	CompanyWeb	Urban
01/07/2020	Sony	20.66	10	49.53	0	CompanyWeb	Urban
01/07/2020	Ecent	22	41	49.66	0	CompanyWeb	Urban
01/07/2020	Ferrari	22.61	33	48.18	34	CompanyWeb	Urban
01/07/2020	Power	22.67	28	48.02	0	CompanyWeb	Urban
01/07/2020	Isaac	19.74	32.5	47.65	0	CompanyWeb	Urban
01/07/2020	BEE	17.99	28	48.2	0	CompanyWeb	Urban
01/07/2020	GP	20.66	29	47.67	41	CompanyWeb	Urban
01/07/2020	CP	18.5	0	45.71	0	CompanyWeb	Urban
03/06/2020	Blackberry	20.06	28	46.69	0	CompanyWeb	Urban
03/06/2020	Eric	19.22	5.5	45.82	0	CompanyWeb	Urban
03/06/2020	Sony	20.66	10	49.53	0	CompanyWeb	Urban
03/06/2020	Ecent	22	41	49.66	0	CompanyWeb	Urban
03/06/2020	Ferrari	22.61	33	48.18	34	CompanyWeb	Urban
03/06/2020	Power	22.67	28	48.02	0	CompanyWeb	Urban
03/06/2020	Isaac	19.74	32.5	47.65	0	CompanyWeb	Urban
03/06/2020	BEE	17.99	28	48.2	0	CompanyWeb	Urban
03/06/2020	GP	20.66	25	47.67	41	CompanyWeb	Urban
03/06/2020	CP	18.5	0	45.71	0	CompanyWeb	Urban
01/05/2020	Blackberry	20.06	28	46.69	0	CompanyWeb	Urban
01/05/2020	Eric	19.22	5.5	45.82	0	CompanyWeb	Urban
01/05/2020	Sony	20.66	10	49.53	0	CompanyWeb	Urban
01/05/2020	Ecent	22.68	36	49.66	0	CompanyWeb	Urban
01/05/2020	Ferrari	22.61	28	48.18	0	CompanyWeb	Urban
01/05/2020	Power	22.67	28	48.02	0	CompanyWeb	Urban
01/05/2020	Isaac	19.74	18	47.65	0	CompanyWeb	Urban
01/05/2020	BEE	17.99	15	48.2	0	CompanyWeb	Urban
01/05/2020	GP	20.66	25	47.67	41	CompanyWeb	Urban
01/05/2020	CP	18.5	0	45.71	0	CompanyWeb	Urban
16/06/2020	GP	14.71	29	58	45	CompanyWeb	Urban
02/06/2020	BEE	12.95	28	48.238	0	CompanyWeb	Urban
02/06/2020	BEE	17.99	0	53.232	0	CompanyWeb	Urban
29/05/2020	Ecent	13.38	41	49.663	0	CompanyWeb	Urban
08/05/2020	Ferrari	16.28	28	48.18	0	CompanyWeb	Urban
08/05/2020	Isaac	13.32	32.5	47.649	0	CompanyWeb	Urban
01/05/2020	Isaac	16.19	18	47.649	0	CompanyWeb	Urban
20/04/2020	GP	15.45	25	28.375	0	CompanyWeb	Urban
09/04/2020	Blackberry	15.12	28	46.693	0	CompanyWeb	Urban
06/04/2020	GP	14.87	28	47.671	0	CompanyWeb	Urban
02/03/2020	Isaac	16.19	18	47.649	0	CompanyWeb	Urban
02/03/2020	Isaac	16.19	0	29.838	0	CompanyWeb	Urban
02/03/2020	Isaac	16.57	0	26.739	0	CompanyWeb	Urban
25/02/2020	Power	16.33	28	48.016	0	CompanyWeb	Urban

@edhans , I started a new thread for the measure. It woud be great if you could help.

Regards,

Supriya