Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Measure to copy the the values of previous date

‎07-22-2020 11:28 AM

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

 

 

 

Pic3.PNG

 

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.

Pic1.PNG

Pic2.PNG

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-

 

DateCompanyRateDiscount(%)Standing RateStanding  Discount(%)SitesRegion
03/01/2020Blackberry6.0161426.560CompanyWebRural
03/01/2020Eric6.0975.528.310CompanyWebRural
03/01/2020Sony6.5401031.660CompanyWebRural
03/01/2020Ecent7.0901530.390CompanyWebRural
03/01/2020Power6.7803028.330CompanyWebRural
03/01/2020Isaac5.9051627.670CompanyWebRural
03/01/2020Ferrari7.1193030.7856.4CompanyWebRural
05/02/2020Sony6.5401031.660CompanyWebRural
05/02/2020Power6.7803028.330CompanyWebRural
05/02/2020Isaac5.9051627.670CompanyWebRural
05/02/2020Blackberry6.0161426.560CompanyWebRural
05/02/2020Eric6.0975.528.310CompanyWebRural
05/02/2020Ecent7.0901530.390CompanyWebRural
05/02/2020Ferrari7.1192030.7838.4CompanyWebRural
26/02/2020Ferrari7.1192830.7856.4CompanyWebRural
02/03/2020Ferrari7.1192830.7856.4CompanyWebRural
02/03/2020Sony6.5401031.660CompanyWebRural
02/03/2020Power6.7803028.330CompanyWebRural
02/03/2020Eric6.0975.528.310CompanyWebRural
02/03/2020Blackberry6.0161426.560CompanyWebRural
02/03/2020Isaac5.9051227.670CompanyWebRural
02/03/2020Ecent7.0901530.390CompanyWebRural
01/04/2020Blackberry6.0161426.560CompanyWebRural
01/04/2020Sony6.5401031.660CompanyWebRural
01/04/2020Power6.7803028.330CompanyWebRural
01/04/2020Isaac5.9051227.670CompanyWebRural
01/04/2020Eric5.3915.528.230CompanyWebRural
01/04/2020Ecent7.0901530.390CompanyWebRural
01/04/2020Ferrari7.1193130.7856.6CompanyWebRural
06/04/2020Ferrari7.1192030.7856.5CompanyWebRural
09/04/2020Blackberry6.0162326.560CompanyWebRural
21/04/2020Ferrari7.1192530.7856.5CompanyWebRural
03/05/2020Blackberry5.6182326.560CompanyWebRural
03/05/2020Eric5.3915.528.230CompanyWebRural
03/05/2020Sony5.791031.660CompanyWebRural
03/05/2020Ecent7.0901530.390CompanyWebRural
03/05/2020Ferrari7.1192530.7856.5CompanyWebRural
03/05/2020Power6.783028.330CompanyWebRural
03/05/2020Isaac5.9051227.670CompanyWebRural
03/06/2020Blackberry5.6182326.560CompanyWebRural
03/06/2020Eric5.3915.528.230CompanyWebRural
03/06/2020Sony5.791031.660CompanyWebRural
03/06/2020Ecent6.241530.390CompanyWebRural
03/06/2020Ferrari7.1192530.7856.5CompanyWebRural
03/06/2020Power6.783028.330CompanyWebRural
03/06/2020Isaac5.9051227.670CompanyWebRural
08/06/2020Blackberry5.6182326.560DirectRural
08/06/2020Eric5.3915.528.230DirectRural
08/06/2020Sony5.791031.660DirectRural
08/06/2020Ecent6.241530.390DirectRural
08/06/2020Ferrari7.1192530.7856.5DirectRural
08/06/2020Power6.783028.330DirectRural
08/06/2020Isaac5.9051227.670DirectRural
01/07/2020Blackberry5.6182626.560CompanyWebRural
01/07/2020Eric5.3915.528.230CompanyWebRural
01/07/2020Sony5.791031.660CompanyWebRural
01/07/2020Ecent6.241530.390CompanyWebRural
01/07/2020Ferrari7.1192530.7856.5CompanyWebRural
01/07/2020Power6.783028.330CompanyWebRural
01/07/2020Isaac5.9051227.670CompanyWebRural
01/07/2020PPP6.072030.9970CompanyWebRural
03/06/2020PPP6.072030.9970CompanyWebRural
03/05/2020PPP6.072030.9970CompanyWebRural
01/04/2020PPP6.072030.9970CompanyWebRural
02/03/2020PPP6.072030.9970CompanyWebRural
03/01/2020PPP6.072030.9970CompanyWebRural
01/07/2020Blackberry20.063246.690CompanyWebUrban
01/07/2020Eric19.225.545.820CompanyWebUrban
01/07/2020Sony20.661049.530CompanyWebUrban
01/07/2020Ecent224149.660CompanyWebUrban
01/07/2020Ferrari22.613348.1834CompanyWebUrban
01/07/2020Power22.672848.020CompanyWebUrban
01/07/2020Isaac19.7432.547.650CompanyWebUrban
01/07/2020BEE17.992848.20CompanyWebUrban
01/07/2020GP20.662947.6741CompanyWebUrban
01/07/2020CP18.5045.710CompanyWebUrban
03/06/2020Blackberry20.062846.690CompanyWebUrban
03/06/2020Eric19.225.545.820CompanyWebUrban
03/06/2020Sony20.661049.530CompanyWebUrban
03/06/2020Ecent224149.660CompanyWebUrban
03/06/2020Ferrari22.613348.1834CompanyWebUrban
03/06/2020Power22.672848.020CompanyWebUrban
03/06/2020Isaac19.7432.547.650CompanyWebUrban
03/06/2020BEE17.992848.20CompanyWebUrban
03/06/2020GP20.662547.6741CompanyWebUrban
03/06/2020CP18.5045.710CompanyWebUrban
01/05/2020Blackberry20.062846.690CompanyWebUrban
01/05/2020Eric19.225.545.820CompanyWebUrban
01/05/2020Sony20.661049.530CompanyWebUrban
01/05/2020Ecent22.683649.660CompanyWebUrban
01/05/2020Ferrari22.612848.180CompanyWebUrban
01/05/2020Power22.672848.020CompanyWebUrban
01/05/2020Isaac19.741847.650CompanyWebUrban
01/05/2020BEE17.991548.20CompanyWebUrban
01/05/2020GP20.662547.6741CompanyWebUrban
01/05/2020CP18.5045.710CompanyWebUrban
16/06/2020GP14.71295845CompanyWebUrban
02/06/2020BEE12.952848.2380CompanyWebUrban
02/06/2020BEE17.99053.2320CompanyWebUrban
29/05/2020Ecent13.384149.6630CompanyWebUrban
08/05/2020Ferrari16.282848.180CompanyWebUrban
08/05/2020Isaac13.3232.547.6490CompanyWebUrban
01/05/2020Isaac16.191847.6490CompanyWebUrban
20/04/2020GP15.452528.3750CompanyWebUrban
09/04/2020Blackberry15.122846.6930CompanyWebUrban
06/04/2020GP14.872847.6710CompanyWebUrban
02/03/2020Isaac16.191847.6490CompanyWebUrban
02/03/2020Isaac16.19029.8380CompanyWebUrban
02/03/2020Isaac16.57026.7390CompanyWebUrban
25/02/2020Power16.332848.0160CompanyWebUrban

 

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

Regards,

Supriya

Labels:
Message 1 of 1
928 Views
0
0 REPLIES 0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All