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.
Hello,
So, I want to create a title with a condition where if the current date >= 15 of the month then show last month(ie Nov) else show the last to last month (October) and the year 2021.
Solved! Go to Solution.
you can try this
Measure =
VAR last=EDATE(today(),-1)
VAR last2=EDATE(today(),-2)
RETURN if(day(today())>=15,FORMAT(last,"mmmm")&" "&year(last),FORMAT(last2,"mmmm")&" "&year(last2))
Proud to be a Super User!
pls try this
Month2 = if(day(today())>=15,FORMAT(EDATE(today(),-1),"mmmm"),FORMAT(EDATE(today(),-2),"mmmm"))
Proud to be a Super User!
you can try this
Measure =
VAR last=EDATE(today(),-1)
VAR last2=EDATE(today(),-2)
RETURN if(day(today())>=15,FORMAT(last,"mmmm")&" "&year(last),FORMAT(last2,"mmmm")&" "&year(last2))
Proud to be a Super User!
@ryan_mayu Can you help me with this how do I add as of date and the same logic till 15 of this month it should show October date ie("As of 10/29/2021") and after 15 it should show the november date ("As of 11/30/2021").
Trying to learn the date dax.
Thanks.
why 10/29/2021, not 10/31/2021?
Measure =
VAR last=EOMONTH(EDATE(today(),-1),0)
VAR last2=EOMONTH(EDATE(today(),-2),0)
RETURN if(day(today())>=15,last, last2)
Proud to be a Super User!
@ryan_mayu Yes! typo it should be last date of the month 10/31/2021 or 11/30/2021. The formula give the 11/16/2021.
I would like to display it like the below image
just updated the DAX. pls double check
measure =
VAR last=EOMONTH(EDATE(today(),-1),0)
VAR last2=EOMONTH(EDATE(today(),-2),0)
VAR _date= if(day(today())>=15,last, last2)
return "As of" & " "&_date
Proud to be a Super User!
you are welcome
Proud to be a Super User!
@ryan_mayu Hey! I am struggling with the dates. Could you please help me.
On the 8th business day of a month onwards I would like to show the data till last month and before the 8th business day I would like to show the data for the month before the last month.
The formula I am using here
CurrentDate = TODAY()
MonthBack_1 = Date( YEAR(DimDate[CurrentDate]-25),MONTH(DimDate[CurrentDate]-25),01)
MonthBack_2 = Date( YEAR(DimDate[CurrentDate]-60),MONTH(DimDate[CurrentDate]-60),01)
EightBusinessday = CALCULATE(MIN(DimDate[Day]),Filter(DimDate, DimDate[CurrentMonthBusinessDay]=8 ))
M_YTDBudget = CALCULATE([M_AnnualBudgetByMonth],FILTER(ALL( DimDate[Month] ), DimDate[Month] <= IF(Day(Now())<EightBusinessday, MAX(Dimdate[monthBack_2]), MAX(Dimdate[monthBack_1]))))
This M_YTD is giving me data for all the months.
Thanks,
Aakanksha
the new question is different the previous one. The previous one is only to show date. the current one is including calculation.
Could you pls provide the sample data and expected output?
Proud to be a Super User!
@ryan_mayu Sure, here is the data.
ProjectName | Month | Budget |
New Clinic Project | 1 | 0 |
New Clinic Project | 2 | 115 |
New Clinic Project | 3 | 247 |
New Clinic Project | 4 | 170 |
New Clinic Project | 5 | 113.5 |
New Clinic Project | 6 | 120.5 |
New Clinic Project | 7 | 192.5 |
New Clinic Project | 8 | 284.69 |
New Clinic Project | 9 | 400.06 |
New Clinic Project | 10 | 303.56 |
New Clinic Project | 11 | 319 |
New Clinic Project | 12 | 126.57 |
Attaching a screenshot - where the end date is showing 11/30/2021. and if I select the EightBusinessday as 15 then the date is 1/24/2022 So, the data should populate till the 11/30/2021 and not till december month. And after 1/24/2022 the data will show till Dec
Now, the data is populating for all the months if I use. I want to filter out the YTD data for till the month of nov.
Any idea how can I achieve that?
Please let me know if you need more info from my end.
Thanks
here is a workaround for you
Measure =
VAR enddate=month(eomonth(today(),-2))
return if(max('Table'[Month])=enddate,1,0)
add this measure to visual filter and set value is 1
Proud to be a Super User!
@ryan_mayu Sorry, But I want to show the data from Jan to Nov and after 15 business day I want to show the data from Jan to dec
i don't have your full data, so I assume you can get the business day.
just update the previous measure and add the workday condition in IF function
Measure =
VAR enddate=month(eomonth(today(),-2))
return if(max('Table'[Month])<=enddate,1,0)
Proud to be a Super User!
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |