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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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

@Anonymous , seem very similar to HR Blog, I have, see if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.