Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abalgir
Helper I
Helper I

Last Month DISTINCTCOUNT returns always blank!

Hello,

 

I am looking to get the number of activities and the average cost for the last month.

 

I have the table [Test] data, structured as follows:

 

Activity_Id  Date                Cost

234             06/03/2019     $2300

237             06/05/2019     $4098

255             06/22/2019     $2300

405             07/15/2019     $2600

701             07/18/2019     $1346

 

I am in July (month 7) and I am looking to get :

 

Month   Activity #  Avg. Cost

June       3              $2899

 

 

Using the formula

CALCULATE(DISTINCTCOUNT(Test[Activity_Id]),PREVIOUSMONTH(Test[Date]))
 returns blank!
I am not able to get any value for the previous month.
 
Any help is appreciated.
 
Many thanks
 

 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

You are welcome.  Try this - https://www.c-sharpcorner.com/article/sort-by-month-name-in-power-bi/


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you and sorry for the late reply.

Is there a way to display in the Slicer the list of months in their natural order : January, February, March, ... December?

The slicer lists them in alphabetical order.

 

Thank you

 

You are welcome.  Try this - https://www.c-sharpcorner.com/article/sort-by-month-name-in-power-bi/


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, it works like a charm

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dax
Community Support
Community Support

Hi abalgir,

 

It seems that you want to calculate previous month and show in current month, right? If so, you could try below measures to see whether it works  or not

Measure =
CALCULATE (
    AVERAGE ( test[Cost] ),
    FILTER (
        ALL ( test ),
        YEAR ( test[date] ) = YEAR ( MIN ( test[date] ) )
            && MONTH ( test[date] )
                = MONTH ( MIN ( test[date] ) ) - 1
    )
)
act =
CALCULATE (
    DISTINCTCOUNT ( test[Activity_Id ] ),
    FILTER (
        ALL ( test ),
        YEAR ( test[date] ) = YEAR ( MIN ( test[date] ) )
            && MONTH ( test[date] )
                = MONTH ( MIN ( test[date] ) ) - 1
    )
)

180.PNG

 

Best Regards,

Zoe Zhi

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.