The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am trying to get the max service date per month and load into a column. I have this working in another workbook but it won't for some reason replicate. My formula is
MaxPerMonth = Calculate(Max(Members_Claims[SERVICE_DT]),FILTER('2017dates','2017dates'[YearMonth] = EARLIER('2017dates'[YearMonth])))
Which is giving me the max of the data in the entire dataset which is 11/1. I am trying to have a column like this:
First Date MaxDataPerMonth
1/1/2017 1/31/2017
2/1/2017 2/28/2017
.....
11/1/2017 11/1/2017 Because we only have data as of 11/1 right now.
Any ideas? Thanks
Solved! Go to Solution.
Thanks for responding. I tried your formula but it did not work but it gave me a jumpstart and I ended up with
DataDay = if(max(Members_Claims[SERVICE_DT])>'2017dates'[Date],Endofmonth(Members_Claims[SERVICE_DT]),MAX(Members_Claims[SERVICE_DT]))
which gave me 1/31/2017, 2/28/2017, .......11/1/2017 which is what I needed but it left me with some blank rows in the DataDay column so used the Summarize function to create a new table that eliminated the blank rows.
Thanks for your help!
HI @shelbsassy,
You can refer to below steps to simply get first date and max date of current month.
Steps:
1. Drag date column to table visual, switch it to hierarchy mode and keep year month.
2. Add above column twice to table visual, switch them to normal date mode and modify summary mode to 'earliest' and 'latest'.
calendar Last Date is 11/9/2017, so last month max date is 11/9/2017.
Regards,
Xiaoxin Sheng
Hi @shelbsassy,
I created this column with this formula where I have got my table Data and its field Date. Please give us a dummy sample if it did not solve your problem.
Test = IF ( MAX ( Data[Date] ) > Data[Date], ENDOFMONTH ( Data[Date] ), MAX ( Data[Date] ) )
Thx
Ninter
Thanks for responding. I tried your formula but it did not work but it gave me a jumpstart and I ended up with
DataDay = if(max(Members_Claims[SERVICE_DT])>'2017dates'[Date],Endofmonth(Members_Claims[SERVICE_DT]),MAX(Members_Claims[SERVICE_DT]))
which gave me 1/31/2017, 2/28/2017, .......11/1/2017 which is what I needed but it left me with some blank rows in the DataDay column so used the Summarize function to create a new table that eliminated the blank rows.
Thanks for your help!
User | Count |
---|---|
79 | |
74 | |
42 | |
30 | |
28 |
User | Count |
---|---|
108 | |
96 | |
53 | |
48 | |
47 |