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
Anonymous
Not applicable

Need help with DAX formula

I am having difficulty in finding out correct way of displaying the below output.

 

I have a table containing columns such as - Contract Name, Contract Start date, Contract end date, Total amount of contract, Status. The output i want to achieve is a bar chart of each month to see which contracts are Live or expired. But each month should carry a total value of contract NOT the allocated value of contract according to number of days in between the start & end date.

 

Example data:

Input

Contracts

Start

End

Amount

Status

Con-01

10/01/2020

26/10/2020

  110,000.00

Expired

Con-02

05/05/2020

09/05/2021

  140,000.00

Expired in last 3 months

Con-03

04/08/2021

26/08/2022

  190,000.00

Not started

Con-04

31/12/2020

13/10/2021

    30,000.00

Live

Con-05

29/02/2020

20/10/2020

    80,000.00

Expired

Con-06

01/05/2020

31/08/2021

  150,000.00

Live

Con-07

25/05/2020

24/05/2021

  185,000.00

Expired in last 3 months

 

DATA in model:

Contracts

Month

 Monthly Amount

Con-01

Jan-20

                    110,000.00

Con-01

Feb-20

                    110,000.00

Con-01

Mar-20

                    110,000.00

Con-01

Apr-20

                    110,000.00

Con-01

May-20

                    110,000.00

Con-01

Jun-20

                    110,000.00

Con-01

Jul-20

                    110,000.00

Con-01

Aug-20

                    110,000.00

Con-01

Sep-20

                    110,000.00

Con-01

Oct-20

                    110,000.00

Con-02

May-20

                    140,000.00

Con-02

Jun-20

                    140,000.00

Con-02

Jul-20

                    140,000.00

Con-02

Aug-20

                    140,000.00

Con-02

Sep-20

                    140,000.00

Con-02

Oct-20

                   140,000.00

Con-02

Nov-20

                    140,000.00

Con-02

Dec-20

                    140,000.00

Con-02

Jan-21

                    140,000.00

Con-02

Feb-21

                    140,000.00

Con-02

Mar-21

                    140,000.00

Con-02

Apr-21

                    140,000.00

Con-02

May-21

                    140,000.00

 So on….

Hence, the output:

shraddhapowerBI_2-1626101072391.png

 

In July-21, the Con-04 & Con-06 is Live whereas Con-02 & Con-07 is Expired in last 3 months.

I want to show Live & expired contracts on each month bar. How can i achieve this?

Thank you.

2 REPLIES 2
Anonymous
Not applicable

Thank you @amitchandak for your response. What i would like to achieve is the total value of each contract should be populated on each bar of the month between the start & end date of that contract. So for example, Con-04 total value is £30,000. The contract start date is 31/12/20 & end date is 13/10/21. So, In bar chart , I want to populate £30,000 in Dec-20 through to Oct-21 (£30,000 should be populated in each month between the contract start & end date).   

amitchandak
Super User
Super User

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.

Top Solution Authors