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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sainig546
Frequent Visitor

Get data for current and last month based on date slicer

Hi,

I am currently working on something which requires me to get data from start of last month to current date  and the data should change based on the month,year selected in slicer. I searched for different ways  but none are giving desired result

 

example data set

machine_idcustomerdate
1000d1 2021-11-15
1001d12021-12-15
1002d22021-06-15
1003d32021-07-15
1004d4

2021-07-16

 

So if we select august 2021 in slicer it should show count of machines as 3 and if nothing is selected in slicer than it should show 2

 

i do not want to change anything in my date slicer

 

I am new to powerbi and not able to get anywhere with this problem 

i am currently using a query similar to this

measure=CALCULATE(DISTINCTCOUNT(Table[machine_id]),DATESMTD('Dates'[Date]))

 

can anybody help me with this ?

Thanks!

6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

Hi, @sainig546 

If we get data for the last month and the current month, when the slicer is not selected, the count is from November to December, then the result is 2. But when August is selected, why is the count 3 instead of 2?
There is data for July but not for August.

vangzhengmsft_0-1640666577607.png

Or what you want to do is accumulate the counts to date?

 

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.



Best Regards,
Community Support Team _ Zeon Zheng


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

hi,

sorry that was my mistake it should show 2 if we select august in slicer i.e for july +august

Hi, @sainig546 

Try to create a measure:

Measure = 
var _date=MAX('Calendar'[Date])
var _Count=
    CALCULATE(
        DISTINCTCOUNT('Table'[machine_id]),
        FILTER(ALL('Table'),'Table'[date]<=_date&&'Table'[date]>EOMONTH(_date,-2)
            )
        )
    
return _Count

Result:

vangzhengmsft_0-1641264376597.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

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

amitchandak
Super User
Super User

@sainig546 , Based on what I got, Try measure like

 

This month =
var _max = maxx(allselected(Date),Date[Date])
return
calculate( DISTINCTCOUNT(Table[machine_id]), filter(all('Date'), 'Date'[Date] <=_max))

 

 

last month =
var _max = eomonth(maxx(allselected(Date),Date[Date]),-1)
return
calculate( DISTINCTCOUNT(Table[machine_id]), filter(all('Date'), 'Date'[Date] <=_max))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi amit,

Can we somehow check in a measure if there is any value selected in the slicer.

For example in most languages we can give if than statement 1 else statement 2

 

using that may be we can provide default value for this measure if nothing is selected in slicer and if a date is selected in slicer than it will be given

 

like if slicer is null than current month else ...

not sure if this is possible here

 

Thanks

can we combine the dataset for both current month and last month in a single measure

Also when i am not selecting any month in slicer its not showing correct data .

if there is no date selection in slicer than it should show data for last month + whatever data we have for this month.

I cant change anything in slicer because i am using report level slicer and this requirement is specific to only this measure rest measures will show all data by default 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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