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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
byWing
Frequent Visitor

How to create visual with reduction on y-axis vs (target date and completion date) on same x-axis

Hi All,

 

I am new to PowerBI and all I have learnt so far is from online videos. Thank you for your help in advance. 

 

I have read through posts here and also watched videos, but I find it difficult to plot this visual. Many recommendations involve some sort of programming which is not possible for me as I have zero programming knowledge.

Here are my questions regarding this table of sample data:

 

Item #PlaceBeforeAfterReductionTarget Date

Target Date

(Financial Quarter)

Completion date

Completion Date

(Financial Quarter)

1A800107901/1/2022FY22Q11/1/2022FY22Q1
2A700506505/1/2023FY23Q27/1/2023FY23Q3
3A6001005009/1/2022FY22Q39/1/2022FY22Q3
4A500104908/5/2022FY22Q312/25/2023FY23Q4
5A8001206805/6/2022FY22Q25/6/2022FY22Q2
6B650506009/4/2022FY22Q39/4/2023FY23Q3
7B455903659/1/2022FY22Q39/1/2023FY23Q3
8B300502504/5/2022FY22Q24/5/2022FY22Q2
9B250601906/5/2022FY22Q210/1/2022FY22Q3
10B250801702/2/2023FY23Q110/2/2023FY23Q4
11C900758254/3/2023FY23Q210/22/2023FY23Q4
12C250102405/5/2023FY23Q212/20/2023FY23Q4
13C133201139/9/2023FY23Q39/9/2023FY23Q3
14C8501084010/10/2023FY23Q410/10/2023FY23Q4
15C7001505506/12/2023FY23Q29/9/2023FY23Q3

 

Question 1: 

Plot the sum of reduction by target date (Financial Quarter) vs completion date (Financial Quarter) by each place.

- In excel, they will look like these 2 charts separately.

- In PowerBI, I struggle to put these 2 charts onto the same time-line (combining both target date and completion date) as I can only select EITHER target date OR Completion Date as the x-axis.

 

byWing_0-1673429942228.png

 

Question 2: 

How to convert dates to Financial Quarter?

- Due to my zero knowledge in programming, I manually set up something like this in my new column:

 

Completion Date (FYQ) = SWITCH(TRUE(),
Dashboard[Completion Date]>=DATE(2022,01,01) && Dashboard[Completion Date]<=DATE(2022,03,30),"FY22Q1"
Dashboard[Completion Date]>=DATE(2022,04,01) && Dashboard[Completion Date]<=DATE(2022,06,30),"FY22Q2"
and so on,
blank()
 
- Totally not wise, but I don't know how to create a vlookup or xlookup in PowerBI.
 

I enjoying learning PowerBI but I think with my zero knowledge in programming, the above 2 questions continue to hinder my progress in my dashboard.

Any insights will be much appreciated. Thank you so much! 😃
2 ACCEPTED SOLUTIONS
v-rongtiep-msft
Community Support
Community Support

Hi @byWing ,

Please have a try.

Create a column.

Column =
VAR _a =
    DATEDIFF ( DATE ( 2021, 9, 3 ), [Date], DAY ) + 1
VAR _b =
    TRUNC ( DIVIDE ( _a, 91 ) )
VAR _c =
    IF ( MOD ( _a, 91 ) = 0, _b, _b + 1 )
VAR _d =
    IF ( MOD ( _c, 4 ) = 0, 4, MOD ( _c, 4 ) )
VAR _e =
    DIVIDE ( _c, 4 )
RETURN
    "FY"
        & 21 + ROUNDUP ( _e, 0 ) & "Q" & _d

vpollymsft_0-1673507149154.png

Best Regards
Community Support Team _ Polly

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

lbendlin
Super User
Super User

 

 

Reduction by Completion = CALCULATE(sum('Table'[Reduction]),USERELATIONSHIP('Table'[Completion Date],Dates[Date]))

 

 

lbendlin_0-1673576804772.png

see attached

 

I created a manual Dates table but you will probably want to supply your own, externally maintained one.  Don't try to create fiscal calendars in Power BI, it is not worth the headache.

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

 

 

Reduction by Completion = CALCULATE(sum('Table'[Reduction]),USERELATIONSHIP('Table'[Completion Date],Dates[Date]))

 

 

lbendlin_0-1673576804772.png

see attached

 

I created a manual Dates table but you will probably want to supply your own, externally maintained one.  Don't try to create fiscal calendars in Power BI, it is not worth the headache.

Thank you @lbendlin 
I used your proposed solution and I think I got it working now. 

😊

-

v-rongtiep-msft
Community Support
Community Support

Hi @byWing ,

Please have a try.

Create a column.

Column =
VAR _a =
    DATEDIFF ( DATE ( 2021, 9, 3 ), [Date], DAY ) + 1
VAR _b =
    TRUNC ( DIVIDE ( _a, 91 ) )
VAR _c =
    IF ( MOD ( _a, 91 ) = 0, _b, _b + 1 )
VAR _d =
    IF ( MOD ( _c, 4 ) = 0, 4, MOD ( _c, 4 ) )
VAR _e =
    DIVIDE ( _c, 4 )
RETURN
    "FY"
        & 21 + ROUNDUP ( _e, 0 ) & "Q" & _d

vpollymsft_0-1673507149154.png

Best Regards
Community Support Team _ Polly

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

v-rongtiep-msft
Community Support
Community Support

Hi @byWing ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _year =
    RIGHT ( YEAR ( MAX ( 'Table'[Target Date] ) ), 2 )
VAR _quarter =
    QUARTER ( MAX ( 'Table'[Target Date] ) )
RETURN
    "FY" & _year & "Q" & _quarter

Or a column.

Column = 
VAR _year =
    RIGHT ( YEAR   ( 'Table'[Target Date] ) , 2 )
VAR _quarter =
    QUARTER  ( 'Table'[Target Date] ) 
RETURN
    "FY" & _year & "Q" & _quarter

vpollymsft_0-1673500970584.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

 

Hi @v-rongtiep-msft 

Thank you so much for your time and help! I truly appreciate. 

I studied your proposed solution and I roughly understand where you are coming from. But I realized the sample data I gave you is assuming every year, 1Q is always starts from 01/01/YY to 03/30/YY and so on. 


Unfortunatley, for our company, the financial calendar looks like this. Q1 start in September and it varies e.g. 09/03/YY and also the last day of the quarter:

 

FY22Q19/3/202112/2/2021
FY22Q212/3/20213/3/2022
FY22Q33/4/20226/2/2022
FY22Q46/3/20229/1/2022
FY23Q19/2/202212/1/2022
FY23Q212/2/20223/2/2023
FY23Q33/3/20236/1/2023
FY23Q46/2/20238/31/2023
FY24Q19/1/202311/30/2023
FY24Q212/1/20232/29/2024
FY24Q33/1/20245/30/2024
FY24Q45/31/20248/29/2024


Pasting it as picture again, as the table looks weird above:

byWing_0-1673503256262.png

 

Based on my situation, what do you recommend I do please? I am open to all possibilities including some simple programming if I need to learn.

Thank you so much!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.