Forum Discussion
Helper IIHelp to create double line chart for YOY comparison with conditions from multiple columns
Hi,
Can someone please help me with the DAX as I can't seem to get it right? I need to create a stacked line charts for YOY to show total rows of PA_Date that is not BLANK (01/01/00) per Month in the Financial Year (starting from July) and column 'RejectionDate is BLANK (01/01/00). Hopefully this makes sense.
Below is my sample data:
| RejectionDate | PA_Date |
| 01/01/00 | 01/01/00 |
| 01/01/00 | 01/01/00 |
| 01/01/00 | 01/01/00 |
| 01/01/00 | 01/01/00 |
| 16/12/24 | 01/01/00 |
| 01/01/00 | 01/01/00 |
| 01/01/00 | 14/08/24 |
| 01/01/00 | 13/08/23 |
| 01/01/00 | 30/09/24 |
| 01/01/00 | 03/06/24 |
| 01/01/00 | 17/08/23 |
| 29/01/24 | 02/07/23 |
| 01/01/00 | 12/04/24 |
| 01/01/00 | 03/09/24 |
| 01/01/00 | 22/12/22 |
| 01/01/00 | 12/04/24 |
| 01/04/25 | 01/04/25 |
| 01/01/00 | 06/12/24 |
| 09/05/25 | 09/05/25 |
| 19/02/25 | 18/02/25 |
| 28/05/24 | 01/01/00 |
| 14/01/25 | 04/09/24 |
| 27/02/25 | 27/02/25 |
| 21/05/25 | 30/09/24 |
| 01/01/00 | 02/07/24 |
| 01/01/00 | 10/09/23 |
| 01/01/00 | 30/07/24 |
| 01/01/00 | 11/11/24 |
| 30/01/24 | 01/11/22 |
| 01/01/00 | 22/07/25 |
| 09/06/25 | 09/06/25 |
| 01/05/24 | 09/11/23 |
| 13/06/25 | 13/06/25 |
| 01/01/00 | 10/07/25 |
| 01/01/00 | 12/06/25 |
| 06/08/24 | 01/05/24 |
| 01/01/00 | 20/08/24 |
| 01/01/00 | 17/06/25 |
| 01/01/00 | 23/08/24 |
| 08/04/24 | 05/08/21 |
| 01/01/00 | 14/05/24 |
| 01/01/00 | 08/06/22 |
| 01/01/00 | 26/11/24 |
| 01/01/00 | 05/12/24 |
| 14/02/24 | 29/08/22 |
| 01/01/00 | 29/07/24 |
| 01/01/00 | 18/06/25 |
| 01/01/00 | 02/07/24 |
| 01/01/00 | 18/06/25 |
| 01/01/00 | 01/01/00 |
| 01/01/00 | 28/02/25 |
| 01/01/00 | 04/07/23 |
| 26/02/25 | 01/01/00 |
| 25/06/25 | 24/06/25 |
| 01/01/00 | 01/04/25 |
| 11/09/24 | 04/09/24 |
| 01/01/00 | 31/07/23 |
| 01/01/00 | 20/09/24 |
| 01/01/00 | 25/06/24 |
| 01/01/00 | 02/07/23 |
| 01/01/00 | 31/03/25 |
| 01/01/00 | 30/09/24 |
| 01/01/00 | 14/10/24 |
| 01/01/00 | 18/02/25 |
| 01/01/00 | 11/07/24 |
| 01/01/00 | 05/12/24 |
| 01/01/00 | 08/11/24 |
| 01/01/00 | 06/11/24 |
| 01/01/00 | 19/09/24 |
| 01/01/00 | 04/11/24 |
| 01/01/00 | 01/01/00 |
| 01/01/00 | 15/07/25 |
| 01/01/00 | 06/02/25 |
| 01/01/00 | 04/09/25 |
| 01/01/00 | 03/06/25 |
| 01/01/00 | 11/07/25 |
| 26/05/25 | 30/09/24 |
| 01/07/25 | 18/02/25 |
| 01/01/00 | 30/09/24 |
| 01/01/00 | 24/09/24 |
| 14/11/24 | 16/05/24 |
| 01/01/00 | 26/05/25 |
| 01/01/00 | 09/09/25 |
| 01/01/00 | 30/09/24 |
| 01/01/00 | 08/07/25 |
| 01/01/00 | 01/01/00 |
| 25/03/25 | 24/03/25 |
| 01/01/00 | 28/02/25 |
| 01/01/00 | 25/08/25 |
I need to be able to create a stacked line chart like the sample below - with no luck. (ps: ignore the vertical line)
What I have done so far:
1. I created a calendar table
Hi Marshmallow ,
Based on your description I believe that your main problem is the measure:
Count_PA = CALCULATE( COUNTROWS(data), Not(ISBLANK(data[RejectionDate])))This calculation gets the values of the Data were the Rejection date is not blank you refer that you want to have the ones where the rejection date is blank so try the following code:
Count_PA = COUNTROWS(FILTER(data, data[RejectionDate] = BLANK()))Then setup your chart with the following:
- X-Axis - Month Name
- Y-Axis - Count_PA
- Legend: Finacial Year
Result below:
1 Reply
- MFelix
Super User
Hi Marshmallow ,
Based on your description I believe that your main problem is the measure:
Count_PA = CALCULATE( COUNTROWS(data), Not(ISBLANK(data[RejectionDate])))This calculation gets the values of the Data were the Rejection date is not blank you refer that you want to have the ones where the rejection date is blank so try the following code:
Count_PA = COUNTROWS(FILTER(data, data[RejectionDate] = BLANK()))Then setup your chart with the following:
- X-Axis - Month Name
- Y-Axis - Count_PA
- Legend: Finacial Year
Result below: