Incorrect Data after adding second measure to Clustered Column Chart?

[powerbinoobster]

So I have this chart in excel that includes the months in 2022 along with the counts. For example, when I sort column Status to  "Complete", I found that there were 49 entries with status of "Complete" in the month of "January" so thats where 49 comes from. Same steps for the "Approved" Column, etc.... My Excel data has "Originated Date", "Approved Date"

Where I am having trouble is when I import that data set in Power bi, I am not sure how to do those manul steps to plot those value in a line and clustered column chart more specifally the setting a shared x-axis.

For Originated Date and Approved Date, I created two new columns that only include the month and the year. I added "Orginated Date - Month" to the shared axis, and when ahead and added my measure to the Column Values in the visualization tool bar:

ORIGINATED_MEASURE = 
CALCULATE (
    COUNT(Clean[Status]),
     Clean[Originated Year] = 2022,
    Clean[Status] = "Approved" || Clean[Status] = "Canceled" || Clean[Status] = "CCB" || Clean[Status] = "CM Review II" || Clean[Status] = "CO Generated" || Clean[Status] = "Complete" 
    || Clean[Status] = "Deferred" || Clean[Status] = "Investigation" || Clean[Status] = "Pending" || Clean[Status] = "Program Review" || Clean[Status] = "Unassigned"
)

This is correct data.

I went ahead and added my Approved Measure:

APPROVED_MEASURE = 
CALCULATE (
    COUNT(Clean[Status]),
    Clean[Approved Year] = 2022,
    Clean[Status] = "Approved" || Clean[Status] = "Complete"
)

BUT I get incorrect values for my Approved Measure because the x-axis is based on the Orginated Date-month and entries which have been approved could have been originated before 2022. I only want to focus on all entries in 2022. Originated AND Approved. Help!

[v-yangliu-msft]

Hi  @powerbinoobster ,

I see that in the two measures, [ORINGINATED_MEASURE] involves ORINGINATED Year, [APPROVED_MEASURE] involves APPROVED Year, you can use try to create a date table as X-axis, then you can

use function

Flag = IF(
    YEAR(MAX('calendarTable'[Date]))=2022,1,0)
Place [Flag]in Filters, set is=1, apply filter.

After that try to use the following function:

ORIGINATED_MEASURE =
CALCULATE (
    COUNT(Clean[Status]),
    FILTER(ALLSELECTED('Clean'),
     Clean[Originated Year] = YEAR(MAX('calendarTable'[Date]))&&
    Clean[Status] = "Approved" || Clean[Status] = "Canceled" || Clean[Status] = "CCB" || Clean[Status] = "CM Review II" || Clean[Status] = "CO Generated" || Clean[Status] = "Complete"
    || Clean[Status] = "Deferred" || Clean[Status] = "Investigation" || Clean[Status] = "Pending" || Clean[Status] = "Program Review" || Clean[Status] = "Unassigned"
))

APPROVED_MEASURE =
CALCULATE (
    COUNT(Clean[Status]),
    FILTER(ALLSELECTED('Clean'),
    'Clean'[Approved Year]=YEAR(MAX('calendarTable'[Date])&&
    Clean[Status] = "Approved" || Clean[Status] = "Complete"
)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

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.