Forum Discussion
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:
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
Anonymous , seem very similar to HR Blog, I have, see if that can help
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).
