Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I use this calc to filter a count of a categor (Shipment ID) using a date field to filter (Dest ETA). This shows me the data required based on last month (monthtToday()=November) so therefore October data. I want to copy/paste this calc to show me only data going back 2 months ie for September ... but cannot seem to get it right. I thought I would just change the -1 to -2?
Any thoughts would be appreciated.
tx
Marc
Hi @MarcUrdang
I create three measures,count of only month 10, count of month 9 and 10, count of only month 9.
Which do you want?
only 10 =
CALCULATE (
COUNT ( Sheet6[id] ),
FILTER (
Sheet6,
Sheet6[date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
&& Sheet6[date]
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) , 1 )
)
)
last two months(9,10) =
CALCULATE (
COUNT ( Sheet6[id] ),
FILTER (
Sheet6,
Sheet6[date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 )
&& Sheet6[date]
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
)
)
only 9 =
CALCULATE (
COUNT ( Sheet6[id] ),
FILTER (
Sheet6,
Sheet6[date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 )
&& Sheet6[date]
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
)
)
If you still have probelms, please show me more details:
eg, the slicers applied on this visual,
what columns added on the visual,
what's the final result you like.
Also perhaps I wasn't clear .. if you look at my first calc ... you will see I count the measure in the calc along with having built in the filter as I need to use this answer in another calculation.
Your previous solution wouldn't help in this regard.
tx but this doesn't seem to work .. the today() is underllined in red and the error says: the fist argument to DATEADD must specify a column
any thoughts?
hi @MarcUrdang
it looks like your initial statement was correct but to filter only september you should not only to change -1 on -2 but try to write "Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), 1 )" as
Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY()) -1, 1 )
nope still no luck .. or perhaps I'm not doing it correcty. Would it be ok for me to email you the workbook to try?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |