cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwild
Frequent Visitor

Calculated Table

Hi,

I have a table in Excel (to be imported into Power Bi) which has dates down one side (running from January 2010 to way into the future)
My columns are vehicles A, B and C (but over 100 of them) and the date within the spreadsheet is cumulative miles

mwild_0-1641398235262.png


I'm looking at creating a new table which will show me the daily miles instead so A would show 232 which is the difference between 15th and 16th

Does anyone know of a way to do this please?



2 ACCEPTED SOLUTIONS
PaulDBrown
Super User
Super User

Firstly I would recommend you Unpivot the Vehicle columns. 

Unpivot.jpg

 

You can then create a calculated column to get the daily mileage for each vehicle using:

 

Daily Mileage =
VAR PrevDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date]
                    = EARLIER ( 'DataTable'[Date] ) - 1
        )
    )
VAR CurrDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date] = EARLIER ( 'DataTable'[Date] )
        )
    )
RETURN
    CurrDate - PrevDate

 

 

To get:

Daily Mielage.jpg

If you want the "Daily Mileage" for the first (minimum) date to be blank, use:

Daily Mileage =
VAR MinDate =
    MIN ( 'DataTable'[Date] )
VAR PrevDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date]
                    = EARLIER ( 'DataTable'[Date] ) - 1
        )
    )
VAR CurrDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date] = EARLIER ( 'DataTable'[Date] )
        )
    )
RETURN
    IF ( 'DataTable'[Date] = MinDate, BLANK (), CurrDate - PrevDate )

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

mwild
Frequent Visitor

does exactly as I needed - thanks for this 👍

 

View solution in original post

3 REPLIES 3
PaulDBrown
Super User
Super User

Firstly I would recommend you Unpivot the Vehicle columns. 

Unpivot.jpg

 

You can then create a calculated column to get the daily mileage for each vehicle using:

 

Daily Mileage =
VAR PrevDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date]
                    = EARLIER ( 'DataTable'[Date] ) - 1
        )
    )
VAR CurrDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date] = EARLIER ( 'DataTable'[Date] )
        )
    )
RETURN
    CurrDate - PrevDate

 

 

To get:

Daily Mielage.jpg

If you want the "Daily Mileage" for the first (minimum) date to be blank, use:

Daily Mileage =
VAR MinDate =
    MIN ( 'DataTable'[Date] )
VAR PrevDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date]
                    = EARLIER ( 'DataTable'[Date] ) - 1
        )
    )
VAR CurrDate =
    CALCULATE (
        SUM ( 'DataTable'[Mileage] ),
        FILTER (
            'DataTable',
            'DataTable'[Vehicle] = EARLIER ( 'DataTable'[Vehicle] )
                && 'DataTable'[Date] = EARLIER ( 'DataTable'[Date] )
        )
    )
RETURN
    IF ( 'DataTable'[Date] = MinDate, BLANK (), CurrDate - PrevDate )

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






mwild
Frequent Visitor

does exactly as I needed - thanks for this 👍

 

mh2587
Super User
Super User

please provide sample data and expected result for the issue



Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors