Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare Dates From Two Reports In The Same Table

Hi

 

There is a weekly report with ongoing projects. I am combining this data into table Backlog. There is a column Report Date which today has three values:

 

  • 25/11/2020
  • 04/12/2020
  • 11/12/2020

One of the columns is called Corr Completion Date.

 

I want to create a matrix that will calculate the difference between these dates across different report dates. 

Report DateProject #Corr Completion Date
11/12/20201234567831/12/2020
04/12/20201234567815/12/2020

 

Example:

The User selects Report Date = 11/12/2020 with a slicer and Project #12345678.

He should see the difference between Corr Completion Dates, so the value = 16 (16 days difference).

 

Does anyone know how to do it?

 

thank you,

Marek

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Please try this measure expression in a table visual with your Project # and Report Date columns

 

Days Change Previous Report =
VAR vThisCompDate =
    MIN ( Projects[Corr Completion Date] )
VAR vThisReportDate =
    MIN ( Projects[Report Date] )
VAR vPrevCompDate =
    CALCULATE (
        LASTNONBLANKVALUE (
            Projects[Report Date],
            MIN ( Projects[Corr Completion Date] )
        ),
        ALLEXCEPT (
            Projects,
            Projects[Project #]
        ),
        Projects[Report Date] < vThisReportDate
    )
RETURN
    DATEDIFF (
        vPrevCompDate,
        vThisCompDate,
        DAY
    )

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Employee
Employee

Please use this expression in place of your current expression (but uses it).

 

NewMeasure = SUMX(SUMMARIZE(Projects, Projects[Project], Projects[Job Site]), [Days Delta WoW])

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat 

 

Actually, I have one more question. do you know how can I fix the total value of days difference? The below screen shows the problem.

 

I have broken down your formula (the one you suggested above) to variables that you can see below as measures (columns in the matrix). I was trying to use something like SUMX, but it didn't work...

 

I would expect the last column to show 750, not 379.

 

Comm.png

Anonymous
Not applicable

Works like a charm - thank you very much.

mahoneypat
Employee
Employee

Please try this measure expression in a table visual with your Project # and Report Date columns

 

Days Change Previous Report =
VAR vThisCompDate =
    MIN ( Projects[Corr Completion Date] )
VAR vThisReportDate =
    MIN ( Projects[Report Date] )
VAR vPrevCompDate =
    CALCULATE (
        LASTNONBLANKVALUE (
            Projects[Report Date],
            MIN ( Projects[Corr Completion Date] )
        ),
        ALLEXCEPT (
            Projects,
            Projects[Project #]
        ),
        Projects[Report Date] < vThisReportDate
    )
RETURN
    DATEDIFF (
        vPrevCompDate,
        vThisCompDate,
        DAY
    )

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.