Count status end of each month

[Anonymous]

I'm new at Power BI, so got a lot of questions - please bear with me I need a measure that will show we the count of open cases by end of each month

DCount_EndOfMonth

CALCULATE (

DISTINCTCOUNT ( P_A_ALL[item no.]);

FILTER(P_A_ALL;P_A_ALL[item status]<>"closed")

)

this obviously doesnt work, i need to add a date field as well, my question is how do i do that, the date field is created date

the result should be e.g. end of januar 2016 open items 60, end of  february 48, end of march 142 etc. ...

[v-yulgu-msft]

Hi @Anonymous,

Suppose your data table is like below. Add two calculated columns using following formulas:

MonthName = P_A_ALL[created date].[Month]
Column = IF(P_A_ALL[item status]="close",1,0)

Then, modify your measure as :

DCount_EndOfMonth =
CALCULATE ( SUM ( P_A_ALL[Column] ), ALLEXCEPT ( P_A_ALL, P_A_ALL[MonthName] ) )

If you have any other question, please feel free to ask.

Best regards,
Yuliana Gu

[Anonymous]

thank you

This is the result i get,

I created the 2 columns and created the measure and added to the chart as line value - unfortunatly this isnt how it should look

So in your eaxample

End of January the number of open cases should be 1, february 3+the one from January = 4  and so on

and this is what i want to show in the line value

[Anonymous]

Is it not possible to attach a pbix file ?

[Vvelarde]

You can upload a file to dropbox, onedrive or another similar and share the link

[Anonymous]

I think it migh be easier to see with the file https://www.dropbox.com/s/rdin1ooef3h9twl/Support_PBIX_File.pbix?dl=0

[Anonymous · Accepted solution]

Thanks to @donsvensen for helping me solve this problem Here is the solution: Akk Measure 1 = CALCULATE([Measur 1];FILTER(ALL(ADATA);ADATA[Created date]<=MAX(ADATA[Created date]))) I had tried with similar, but I didnt use Measure 1, which is the measure i created to make the distinct count Thanks again