Merging multiple TotalYTD visualizations.

[cjsund]

Hi everyone,

I have a tracking sheet for 4 different stages of work, and each stage has it's own respective totalYTD visual.  But now I want to have a single visual that shows all 4 stages on one graph, but I can't figure out how to pull all the data onto one graph because they all have a unique date source for the X axis.

I was able to get the individual visuals to work with 

DR Status YTD =

IF(

    ISFILTERED('Master List'[DR Completion]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    TOTALYTD(

        COUNTA('Master List'[DR Status]),

        'Master List'[DR Completion].[Date]

    )

)

The above is an example of 3 of the different totalytd charts that I would like to merge into one visualization and then filter out by "Completed" from their respective columns.  The following chart being an example of what the tracking data looks like.

AR Status	AR Completion	CR Status	CR Completion	DR Status	DR Completion	WI status	WI Completion
Review	21-Aug	revisions	24-Aug
Complete	02-Aug	complete	05-Aug	completed	22-Aug	revisions	27-Aug
Revisions	21-Aug	in progress	26-Aug
Complete	13-Aug	complete	19-Aug	review	23-Aug
Review	20-Aug	review	24-Aug
Hold
In Progress	25-Aug	in progress	30-Aug
Pending	03-Sep	pending	06-Sep
Pending	06-Sep	pending	09-Sep
Revisions	23-Aug	revisions	26-Aug
In Progress	30-Aug	in progress	02-Sep
Pending	30-Aug	pending	03-Sep
Pending	29-Aug	pending	01-Sep
Pending	06-Sep	pending	09-Sep
Revisions	23-Aug	In progress	25-Aug
Revisions	22-Aug	pending	24-Aug
Revisions	20-Aug	revisions	23-Aug
Pending	30-Aug	pending	03-Sep
Pending	06-Sep	pending	09-Sep
review	15-Aug	review	20-Aug	on hold
complete	23-Jul	complete	26-Jul	Revisions	09-Aug

 I've run out of ideas how I can try to fit these all onto a single visualization just so I can have a graph showing the cumulative progress across all complete projects.  As this will encompass hundreds of rows eventually.

Thanks for the help!

[Anonymous · Accepted solution]

Thanks for the reply from amitchandak , please allow me to provide another insight:

Hi  @cjsund ,

I created four tables and four corresponding measure:

Here are the steps you can follow：

If you want to display the values of all four line charts on the same axis, you can simply create a table of dates to be used as a bridge table with the dates of the other four tables in a join relationship.

Using the Line chart, use the [Date] of the date table for the X-axis, and place the four measures in the Y-axis.

If you want to display different four line charts in the same visual, you can try to create a Group_Table and place it in Small multiples using [Group] as a category.

Enter data – create a table.

Create measure.

Measure =
SWITCH(
    TRUE(),
    MAX('Group_Table'[Group])= "Measure1",[Measure1]
    MAX('Group_Table'[Group])= "Measure2",[Measure2],
    MAX('Group_Table'[Group])= "Measure3",[Measure3],
    MAX('Group_Table'[Group])= "Measure4",[Measure4]
)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@cjsund , With help from date table, create a measure like below. And use status as a legend on the visual. Having a separate date/calendar table is important. Join date of Date table with the date of your table and use columns from date table in measure, visual and slcier

  TOTALYTD(

        COUNTA('Master List'[DR Status]),

        'Date'[Date]

    )

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.