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
RudyL
Helper I
Helper I

Mixing dates from columns with dates in a slicer

Hi,

A dates-challenge here. I have an invoice table like the simple one at the bottom of this post. I added a slicer from a date in a calendar table to look at a certain point in time in this history. So the slicer filters the invoices. 

 

But I also want the date value in the slicer to be a reference value for a calculation of open invoices. 

For example: when i look at reference date 22-1 only 200 is unpaid, and it's in a category less than 1 month.

 

reference date22-1-2020 
open < 1 month200from invoice I1
open > 1 month0

 

 

But when I take a reference date 2-4, there's 1000 open from recent invoices in the past month and 200 from earlier invoices.

 

reference date2-4-2020 
open < 1 month1000from invoice I4
open > 1 month200from invoice I2

 

And finally when i set the reference date to 2-7, all is well

reference date2-7-2020
open < 1 month0
open > 1 month0

 

I managed to make the calculated columns when I used the NOW() function as a reference date. But when I try to use the reference date from a slicer in a measure like max(calendar.date) the calculated columns don't return the results I expected. I read something about not using measures in calculated columns. But I'm stuck now. Anybody wants to help me on the calculated columns and measures needed for this?

 

Here's the table:

 

DateInvoiceInvoicedPaid
1-1-2020I110000
2-1-2020I10800
1-2-2020I210000
3-2-2020I20800
1-3-2020I310000
3-3-2020I301000
3-3-2020I20200
1-4-2020I410000
5-4-2020I401000
5-4-2020I10200
1-5-2020I510000
1-6-2020I610000
5-6-2020I601000

 

1 ACCEPTED SOLUTION

Thanks Resident Rockstar. Santa does exist !!! 😁 

Also appreciate you deliver the answer in a way that I can learn from it.

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

Hi, @RudyL , you might want to try the pattern in the attached file.

Screenshot 2020-12-24 010326.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Thanks Resident Rockstar. Santa does exist !!! 😁 

Also appreciate you deliver the answer in a way that I can learn from it.

amitchandak
Super User
Super User

@RudyL , You have try like this for date slicer from Date/Calendar Table

refer example

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
averageX(values(Table[invoice]), datediff(max(table[Date]),_max,day))

 

 

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

Thanks for this part of the solution. Very helpful.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

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.