Solved: Show visual total % for current and previous perio...

Anonymous · ‎01-18-2022

Dear community,

I have problems with calculating the visual total % of work effort for the previous period using DATEADD.

I have some data which shows how much time is spent for each work activity ('Workdetails') If the work activity has lasted several days, the hours are distributed evenly for the dates ('Work effort distributed (h)').

The requirement is to compare the work effort of the current period and the previous period. The data should be slicable and still show 100% in the visual for any selected time period / slicer selections.

I have the following type of data:

Year

Quarter

Month

Day

Workid

Country

PL

Workdetails

StartDate

EndDate

DistributedDates

Effort_Hours+Minutes

Work effort distributed (h)

2021

Qtr 4

October

27

1

United Arab Emirates

SAAB

Training partners

27.10.2021 0:00

4

2021

Qtr 4

December

12

2

American Samoa

TEAM

Personal development

12.12.2021 0:00

14.12.2021 0:00

12.12.2021 0:00

26

8.666666666666666

2021

Qtr 4

December

13

2

American Samoa

TEAM

Personal development

12.12.2021 0:00

14.12.2021 0:00

13.12.2021 0:00

26

8.666666666666666

2021

Qtr 4

December

14

2

American Samoa

TEAM

Personal development

12.12.2021 0:00

14.12.2021 0:00

26

8.666666666666666

2021

Qtr 4

December

20

3

Belgium

SAAB

Project support

20.12.2021 0:00

1

2021

Qtr 4

December

20

4

Luxembourg

MASI

Training / office

20.12.2021 0:00

4

2021

Qtr 4

December

20

5

Belgium

SAAB

Training / remote

20.12.2021 0:00

2

2021

Qtr 4

December

20

6

Belgium

SAAB

Demo / on-site

20.12.2021 0:00

1

2022

Qtr 1

January

4

7

Lithuania

MASI

Partner support

4.1.2022 0:00

1

I also have a custom Calendar table ('Date') linked to my data table ('DistiributedDates').

I'm trying to show how the work effort (in %) differs between the current and previous periods (Year, Quarter, Month, Day).
This bar chart below is basically the visualization that I'm aiming for, but instead of showing the percentage of grand total work effort (of the whole data), the bars should show the visual total - i.e. reacting to slicers on the page, and still providing 100%:

The work effort amount I'm using as a basis is in the column: 'Work effort distributed (h)'
The grand total of work effor is in the measure called 'EffortInHoursTotal':

EffortInHoursTotal = 
CALCULATE(
    SUM('demodata'[Work effort distributed (h)]),
     REMOVEFILTERS('demodata') 
    )

I have a measure which calculates the Work effort %

Effort (%) = 
DIVIDE(
     sum('demodata'[Work effort distributed (h)]),  
     [EffortInHoursTotal])

Then I have a measure which uses the above measure and calculates the Previous period work effort %.
It uses a dynamic time period selection solution that I found from some blog/video.

Previous Period Effort % = 
SWITCH(
    TRUE(),
    ISINSCOPE('Calendar'[Date].[Day]),
    CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, DAY)
    ), 
    ISINSCOPE('Calendar'[Date].[Month]),
    CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, MONTH)
    ), 
        ISINSCOPE('Calendar'[Date].[Quarter]),
    CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, QUARTER)
    ), 
        ISINSCOPE('Calendar'[Date].[Year]),
    CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, YEAR)
    )
)

As far as I understand, these basic % calculations seem to work OK (and are actually used in the above bar chart).
But as said, I should not visualize these but "visual total %'.

So, next I've created a measure to calculate the visual total %:

Effort % ALLSELECTED = 
DIVIDE(
[Effort (%)],
CALCULATE(
   [Effort (%)],
    ALLSELECTED( demodata )
))

Not sure if it's perfect but I'm able to show this in the visual and it reacts to the slicers, showing all the time total 100%.

The measure that I just cannot create is to show the visual total % for the previous period.
This attempt shows 100% bars for all periods:

Previous Period Effort % ALLSELECTED = 

SWITCH(
    TRUE(),
    ISINSCOPE('Calendar'[Date].[Day]),   
    CALCULATE(
      [Effort % ALLSELECTED],
       DATEADD('Calendar'[Date].[Date], -1, DAY)
    ),
    ISINSCOPE('Calendar'[Date].[Month]), 
    CALCULATE(
        [Effort % ALLSELECTED],
         DATEADD('Calendar'[Date].[Date], -1, MONTH)
    ), 
    ISINSCOPE('Calendar'[Date].[Quarter]),
    CALCULATE(
      [Effort % ALLSELECTED],
       DATEADD('Calendar'[Date].[Date], -1, QUARTER)
    ), 
    ISINSCOPE('Calendar'[Date].[Year]),
    CALCULATE(
      [Effort % ALLSELECTED],
       DATEADD('Calendar'[Date].[Date],-1, YEAR)
    )
)

I also tried to use the Effort % calculation in VAR but this ignores DATEADD:

Previous Period Effort % ALLSELECTED VAR = 

VAR _selectedDistribution = 
 [Effort (%)]/
CALCULATE(
     [Effort (%)],
    ALLSELECTED( demodata )
    ) 

RETURN

SWITCH(
    TRUE(),
    ISINSCOPE('Calendar'[Date].[Day]),   
    CALCULATE(
      _selectedDistribution,
        DATEADD('Calendar'[Date], -1, DAY)
    ),
    ISINSCOPE('Calendar'[Date].[Month]), 
    CALCULATE(
        _selectedDistribution ,
         DATEADD('Calendar'[Date], -1, MONTH)
    ), 
    ISINSCOPE('Calendar'[Date].[Quarter]),
    CALCULATE(
      _selectedDistribution,
        DATEADD('Calendar'[Date], -1, QUARTER)
    ), 
    ISINSCOPE('Calendar'[Date].[Year]),
    CALCULATE(
      _selectedDistribution,
        DATEADD('Calendar'[Date],-1, YEAR)
    )
)

I quess it is probably some context issue but I don't understand what and how to fix it.

Here's a table with all those above measures:

I hope you understood what I'm trying to do here?

I would really appreciate your expertise because I've stuggled with this a looong time...

BR,
Minna
pbix: Demo.pbix
https://www.dropbox.com/s/lj80e3wja9o5yhm/Demo.pbix?dl=0

v-yanjiang-msft · ‎01-20-2022

Hi @Anonymous ,

According to your sample, can see, there's a total value of Effort(%), but there isn't a total value of Previous Period Effor%, that's why can't directly use the below formula like Effort % ALLSELECTED.

Previous Period Effort % ALLSELECTED= 
DIVIDE([Previous Period Effort %],CALCULATE([Previous Period Effort %],ALLSELECTED(demodata)))

As for why there isn't a total value of Previous Period Effor%, because the ISINSCOPE function in your formula, there isn't a date in the total row, so it shows blank.

Here's my solution.

Previous Period Effort % = 
SWITCH(
    TRUE(),
    ISINSCOPE('Calendar'[Date].[Day])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, DAY))
    , 
    ISINSCOPE('Calendar'[Date].[Month])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, MONTH)
    ), 
    ISINSCOPE('Calendar'[Date].[Quarter])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, QUARTER)
    ), 
    ISINSCOPE('Calendar'[Date].[Year])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, YEAR))
    )

Previous Period Effort % ALLSELECTED= 
DIVIDE([Previous Period Effort %],CALCULATE([Previous Period Effort %],ALLSELECTED(demodata)))

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yanjiang-msft · ‎01-20-2022

Hi @Anonymous ,

According to your sample, can see, there's a total value of Effort(%), but there isn't a total value of Previous Period Effor%, that's why can't directly use the below formula like Effort % ALLSELECTED.

Previous Period Effort % ALLSELECTED= 
DIVIDE([Previous Period Effort %],CALCULATE([Previous Period Effort %],ALLSELECTED(demodata)))

As for why there isn't a total value of Previous Period Effor%, because the ISINSCOPE function in your formula, there isn't a date in the total row, so it shows blank.

Here's my solution.

Previous Period Effort % = 
SWITCH(
    TRUE(),
    ISINSCOPE('Calendar'[Date].[Day])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, DAY))
    , 
    ISINSCOPE('Calendar'[Date].[Month])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, MONTH)
    ), 
    ISINSCOPE('Calendar'[Date].[Quarter])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, QUARTER)
    ), 
    ISINSCOPE('Calendar'[Date].[Year])||[Effort % ALLSELECTED]=1,
     CALCULATE(
        [Effort (%)],
        DATEADD('Calendar'[Date].[Date], -1, YEAR))
    )

Previous Period Effort % ALLSELECTED= 
DIVIDE([Previous Period Effort %],CALCULATE([Previous Period Effort %],ALLSELECTED(demodata)))

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎01-21-2022

Hi @v-yanjiang-msft

Oh, what a clever solution, wouldn't have figured this out myself - ever!
Thank you ever so much, you're brilliant! 🙂

BR,
Minna

Show visual total % for current and previous period

Helpful resources

Microsoft Fabric & AI Learning Hackathon

How to Get Your Question Answered Quickly