Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Previous month data that should appear on the card (in this case JUNE):
Pbix file:
https://1drv.ms/u/s!Avq1zGJizBimsUCECKbCmKD7UL7g?e=yZmBqc
Thanks for all.
Regards,
Denis.
Solved! Go to 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!
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!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |