project count of every month last date and current month current date

[Anonymous]

I am new to power bi, Need to find a the  active count of project on every month last date and current month current date. Kindly help me on this. 

Here the count of project for the whole month, but i need only the last day of the month count and current day of current month

[parry2k · Accepted solution]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

Add following measure and change table and column name as per your model

Total Count = 
VAR __date = MIN ( EOMONTH ( DateTable[Date] ), TODAY() )
RETURN
CALCULATE (
COUNTROWS ( ProjectTable ),
DateTable[Date] = __date
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k @amitchandak @v-alq-msft 

Thank you all, I was able to submit the report on time.  😍

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

DimDate(a calculated table):

DimDate = CALENDARAUTO()

There is a relationship between two tables. You may try the following measure to see if it helps.

Result1 = 
var _enddate = MIN(EOMONTH(MAX(DimDate[Date]),0),TODAY())
return
COUNTROWS(
    FILTER(
        'Table',
        'Table'[Date]=_enddate
    )
)

Result2 = 
var _enddate = MIN(EOMONTH(MAX(DimDate[Date]),0),TODAY())
return
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        'Table',
        'Table'[Date]=_enddate
    )
)

Result:

Best Regards

Allan

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

[Anonymous]

 @v-alq-msft Thanks you so much. its working

[parry2k]

@Anonymous that's lovely. Glad to hear!

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[amitchandak]

@Anonymous , closingbalancemonth for month end date value

You can use https://docs.microsoft.com/en-us/dax/closingbalancemonth-function-dax

measure =
var _max = maxx(allselected(Date),Date[Date])
return
if(Max(Date[Date])<=Eomonth(Today,-1),closingbalancemonth (Sum(Table[Value]),Date[Date]), calculate(Sum(Table[Value]), Date[Date]=_max))

[Anonymous]

@amitchandak 

As i am looking to take the count, i need to replace the SUM experssion with Count right ?

[parry2k]

@Anonymous not sure why it wouldn't work, try updating using following 

Month End Value = 
VAR __endDate = MIN ( EOMONTH ( MAX ( 'Date'[Date] ), 0 ), TODAY() )
RETURN
CALCULATE ( COUNTROWS ( ProjectTable ), KEEPFILTERS ( 'Date'[Date] = __endDate ) )

if it doesn't work share your DAX expression.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k 

its not working. 

this is the dax which i am using 

Test count = VAR __endDate = MIN ( EOMONTH ( MAX ( DimDate[DateKey] ), 0 ), TODAY() )

RETURN

CALCULATE ( COUNTROWS ( FactPortfolio ), KEEPFILTERS ( DimDate[DateKey] = __endDate ) )

[parry2k]

@Anonymous replace DateKey with actual Date column

[parry2k · Accepted solution]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

Add following measure and change table and column name as per your model

Total Count = 
VAR __date = MIN ( EOMONTH ( DateTable[Date] ), TODAY() )
RETURN
CALCULATE (
COUNTROWS ( ProjectTable ),
DateTable[Date] = __date
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k 

 Thank for the quick reply. 

For your information we already have a dim date table. I tried to replicate the measure but no count it showing. 

Here DateKey is YYYY-MM-DD format

Now the report is not showing value