Need help with DAX formula

Anonymous — Mon, 12 Jul 2021 14:50:24 GMT

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:

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.

Re: Need help with DAX formula

amitchandak — Mon, 12 Jul 2021 15:49:13 GMT

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

Re: Need help with DAX formula

Anonymous — Tue, 13 Jul 2021 09:38:07 GMT

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).

topic Need help with DAX formula in DAX Commands and Tips

Need help with DAX formula

Re: Need help with DAX formula

Re: Need help with DAX formula