Forum Discussion

ArslanManzoor's avatar
ArslanManzoor
Frequent Visitor
5 years ago
Solved

3 month rolling data visual

Hello!   Please can someone help with a problem I am having with Power BI. I have 2 tables below (data sources), the first table is showing the time taken to do tasks A and B over a few months, thi...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, ArslanManzoor 

    Please check the below steps.

     

    Write a measure something like below.

     

    Conditional Format measure =
    SWITCH (
    TRUE (),
    [Time Taken Avg for Visualization]
    > 1.05 * SELECTEDVALUE ( Target[Target] ), 1,
    [Time Taken Avg for Visualization]
    <= 1.05 * SELECTEDVALUE ( Target[Target] )
    && [Time Taken Avg for Visualization]
    >= 0.95 * SELECTEDVALUE ( Target[Target] ), 2,
    [Time Taken Avg for Visualization]
    < 0.95 * SELECTEDVALUE ( Target[Target] ), 3
    )
     
    Setup like below.
     
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, ArslanManzoor 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

In order to show "Current" in the column in Matrix Visualization, I suggest creating an Axis table to show it like below.

 

 

Time Taken Avg for Visualization =
VAR todaymonthyear =
CALCULATE (
MAX ( 'Calendar'[Month & Year] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = TODAY () )
)
VAR previousmonthendoftoday =
EOMONTH ( TODAY (), -1 )
VAR fourmonthbeforeenddate =
EOMONTH ( TODAY (), -4 )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( AxisTable[Month & Year] ) = "Current",
CALCULATE (
[Time Taken Avg],
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month & Year] <> todaymonthyear )
),
MAX ( 'Calendar'[Date] ) <= previousmonthendoftoday
&& MIN ( 'Calendar'[Date] ) > fourmonthbeforeenddate,
CALCULATE (
[Time Taken Avg],
DATESINPERIOD (
'Calendar'[Date],
EOMONTH ( MAX ( 'Calendar'[Date] ), -1 ),
-3,
MONTH
)
)
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

ArslanManzoor's avatar
ArslanManzoor
Frequent Visitor
5 years ago

Hi Jihwan_Kim 

Thanks very much for your response!

How do I create an axis table? is this a custom visual?