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

[byWing]

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 #	Place	Before	After	Reduction	Target Date	Target Date (Financial Quarter)	Completion date	Completion Date (Financial Quarter)
1	A	800	10	790	1/1/2022	FY22Q1	1/1/2022	FY22Q1
2	A	700	50	650	5/1/2023	FY23Q2	7/1/2023	FY23Q3
3	A	600	100	500	9/1/2022	FY22Q3	9/1/2022	FY22Q3
4	A	500	10	490	8/5/2022	FY22Q3	12/25/2023	FY23Q4
5	A	800	120	680	5/6/2022	FY22Q2	5/6/2022	FY22Q2
6	B	650	50	600	9/4/2022	FY22Q3	9/4/2023	FY23Q3
7	B	455	90	365	9/1/2022	FY22Q3	9/1/2023	FY23Q3
8	B	300	50	250	4/5/2022	FY22Q2	4/5/2022	FY22Q2
9	B	250	60	190	6/5/2022	FY22Q2	10/1/2022	FY22Q3
10	B	250	80	170	2/2/2023	FY23Q1	10/2/2023	FY23Q4
11	C	900	75	825	4/3/2023	FY23Q2	10/22/2023	FY23Q4
12	C	250	10	240	5/5/2023	FY23Q2	12/20/2023	FY23Q4
13	C	133	20	113	9/9/2023	FY23Q3	9/9/2023	FY23Q3
14	C	850	10	840	10/10/2023	FY23Q4	10/10/2023	FY23Q4
15	C	700	150	550	6/12/2023	FY23Q2	9/9/2023	FY23Q3

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.

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! 😃

[Anonymous · Accepted solution]

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

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.

[lbendlin · Accepted solution]

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

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.

[byWing]

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

😊

[byWing]

-

[Anonymous · Accepted solution]

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

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.

[Anonymous]

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

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.

[byWing]

Hi @Anonymous 

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:

FY22Q1	9/3/2021	12/2/2021
FY22Q2	12/3/2021	3/3/2022
FY22Q3	3/4/2022	6/2/2022
FY22Q4	6/3/2022	9/1/2022
FY23Q1	9/2/2022	12/1/2022
FY23Q2	12/2/2022	3/2/2023
FY23Q3	3/3/2023	6/1/2023
FY23Q4	6/2/2023	8/31/2023
FY24Q1	9/1/2023	11/30/2023
FY24Q2	12/1/2023	2/29/2024
FY24Q3	3/1/2024	5/30/2024
FY24Q4	5/31/2024	8/29/2024

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

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!