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
deniselias
Frequent Visitor

Date count of previous month Blank - (Pbix file in link)

Hello,

 

Could someone help me to make a previous month measure that doesn't consider blank?

 

I've tried various DAX expressions and Variables, but I can't find a solution that considers counting without the blank.

I don't just want to place a segmented selection but I want it to be displayed on the card.

 

data displayed:

deniselias_0-1657951027248.png

 

Previous month data that should appear on the card (in this case JUNE):

deniselias_1-1657951350151.png

 

 

Pbix file:

https://1drv.ms/u/s!Avq1zGJizBimsUCECKbCmKD7UL7g?e=yZmBqc

 

Thanks for all.

 

Regards,

Denis.

1 ACCEPTED SOLUTION

Hello @lbendlin,

 

Thanks for replying to this post,

 

But I had another post open for the same problem and the solution that was presented was the best possible one and helped me with my problem.

I also tested this solution that you presented but it didn't work.

 

Solution by @tamerj1 :

https://community.powerbi.com/t5/Desktop/Date-count-of-previous-month-Blank-HELP/m-p/2643160#M928130

MIRO count LastMonth 2 = 
VAR SelectedDate = TODAY ( )
VAR StartOfPreviousMonth = EOMONTH ( SelectedDate, - 2 ) + 1
VAR EndOfPreviousMonth = EOMONTH ( StartOfPreviousMonth, 0 )
VAR Result =
    CALCULATE (
        COUNT ( 'P  Manual'[MIRO date] ),
        'P  Manual'[MIRO date] >= StartOfPreviousMonth,
        'P  Manual'[MIRO date] <= EndOfPreviousMonth,
        'P  Manual'[Status] = "6.1 PAYMENT RELEASED",
        REMOVEFILTERS ( 'P  Manual' )
    )
RETURN
    Result

 

Thanks!

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

 

MIRO total count := 
CALCULATE(COUNT('P  Manual'[Status]), 'P  Manual'[Status]="6.1 PAYMENT RELEASED", 'P  Manual'[MIRO date]<>BLANK())

MIRO count LastMonth := 
CALCULATE(COUNT('P  Manual'[Status]),ALL('P  Manual'), 'P  Manual'[Status]="6.1 PAYMENT RELEASED", 'P  Manual'[MIRO date]<>BLANK(),dateadd('P  Manual'[MIRO date],-1,MONTH))

 

Hello @lbendlin,

 

Thanks for replying to this post,

 

But I had another post open for the same problem and the solution that was presented was the best possible one and helped me with my problem.

I also tested this solution that you presented but it didn't work.

 

Solution by @tamerj1 :

https://community.powerbi.com/t5/Desktop/Date-count-of-previous-month-Blank-HELP/m-p/2643160#M928130

MIRO count LastMonth 2 = 
VAR SelectedDate = TODAY ( )
VAR StartOfPreviousMonth = EOMONTH ( SelectedDate, - 2 ) + 1
VAR EndOfPreviousMonth = EOMONTH ( StartOfPreviousMonth, 0 )
VAR Result =
    CALCULATE (
        COUNT ( 'P  Manual'[MIRO date] ),
        'P  Manual'[MIRO date] >= StartOfPreviousMonth,
        'P  Manual'[MIRO date] <= EndOfPreviousMonth,
        'P  Manual'[Status] = "6.1 PAYMENT RELEASED",
        REMOVEFILTERS ( 'P  Manual' )
    )
RETURN
    Result

 

Thanks!

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.