Cancelled vs Active in Column Chart

[sgetgood]

Hi all,

I have a table, Gifts, with a Status field that can be either Cancelled or Active. The original setup is shown by the field Type = Recurring Gift, and the payments to the gift are of type = Recurring Gift Payment. There are both gift date and gift status date fields.

Gift ID	Gift Date	Gift Type	Gift Status	Gift Status Date	Gift Instalment Frequency
1	01/06/2021	Recurring Gift	Cancelled	09/02/2024	Monthly
2	09/06/2021	Recurring Gift Payment	Active		Monthly
3	09/06/2021	Recurring Gift Payment	Active		Monthly
4	01/06/2021	Recurring Gift Payment	Active		Monthly

I want to show how many payments were made at any point in time, so recurring gift payments with status active, and Gift Date in the x axis. This is simple enough.

I also want to show in the same chart, recurring gifts with status cancelled by Gift Status Date as negative values. This is where I'm getting stuck.

Is this possible?

[speedramps]

Thank you @sgetgood 

👍

[speedramps]

Hi @sgetgood 

Please can you also accept my solution.

I provided it first and with examples.

It is polite and sensible to click [accept solution] and the thumbs up to reward helpers.

Thank you

[speedramps]

You can create DAX measures ...

activecount = 
CALCULATE(
    COUNTROWS(yourdata),
    yourdata[Gift Status] = "Active")

cancelledcount = 
CALCULATE(
    COUNTROWS(yourdata),
    yourdata[Gift Status] = "Cancelled")

* - 1    

Please click thumbs up because I havce tried to help.

Then click accept solution if it fixed your problem, 

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want

* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[speedramps]

You could add a column or columns in Power Query

[bnjmnnl · Accepted solution]

Hello! You can add a column with an IF statement as: if status = active, 1, if status = cancelled, -1. THen use this column as the value in the Y-axis. Then you can use status as a legend to seperate them etc. Active will only have + values and cancelled will only have - values