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
Vanrajr
Frequent Visitor

Previous Weeks measure

Hi all,

 

Hoping someone can help! I'm trying to essentially create a KPI which compares this weeks total sum data to last week's total sum data.

 

MY data is essentially as below. So I would want the KPI to show that in week 1 total project hours was 25, In week 2 it was 30 which is a increase of +5. Then in Week 3 it increased by +5 again but this would only be displayed in week 3 etc.

 

your help is appreicated! I've been messing around with this measure but cant get it working:

 

Previous Week Project Hours = CALCULATE(SUM(WorkLog[HoursWorked]),WorkLog[Project Y/N] = "Y", CALENDAR(WorkLog[Start of Week] -7

 

2017-11-27 13-51-39_Book1 - Excel.png

2 ACCEPTED SOLUTIONS
SteveCampbell
Memorable Member
Memorable Member

Is that part of your table in the picture?
If so, you could:

 

 

Previous Week Project Hours =

VAR LastWeek =
    MIN ( WorkLog[Week Num] ) - 1


RETURN
    CALCULATE (
        SUM ( WorkLog[Hours] )
      )
    - 

      CALCULATE 

            ( SUM ( WorkLog[Hours] )

             WorkLog[Week Num] = LastWeek )
             )



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

@Vanrajr,

 

Please try the DAX below.

Previous Week Project Hours =
VAR LastWeek =
    MIN ( WorkLog[Week Num] ) - 1

RETURN
      CALCULATE 
            ( SUM ( WorkLog[Hours] ), 
             WorkLog[Week Num] = LastWeek )
             )

If this is not what you want, please provide us more information about your report, so that we can make further analysis. You can upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

 

Regards,
Charlie Liao

 

 

View solution in original post

5 REPLIES 5
SteveCampbell
Memorable Member
Memorable Member

Is that part of your table in the picture?
If so, you could:

 

 

Previous Week Project Hours =

VAR LastWeek =
    MIN ( WorkLog[Week Num] ) - 1


RETURN
    CALCULATE (
        SUM ( WorkLog[Hours] )
      )
    - 

      CALCULATE 

            ( SUM ( WorkLog[Hours] )

             WorkLog[Week Num] = LastWeek )
             )



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Thank you Steve! 🙂

Oooo! Thank you so much for that. So It looks like it pulled the data in but the data looks to be incorrect. The Goal should display 563.50 in this case and then work out the corresponding % increase/decrease if that makes sense.

 

image.pngimage.png

@Vanrajr,

 

Please try the DAX below.

Previous Week Project Hours =
VAR LastWeek =
    MIN ( WorkLog[Week Num] ) - 1

RETURN
      CALCULATE 
            ( SUM ( WorkLog[Hours] ), 
             WorkLog[Week Num] = LastWeek )
             )

If this is not what you want, please provide us more information about your report, so that we can make further analysis. You can upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

 

Regards,
Charlie Liao

 

 

@v-caliao-msft 

 

Thank you so much! 🙂

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.