Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
On the 8th business day of a month onwards I would like to show the data till last month (Dec)and before the 8th business day I would like to show the data for the month before the last month(Nov).
Solved! Go to Solution.
Hi, @Anonymous ;
You could add another measure as rank Business day measure. then modify your meaure.
1. rank measure.
rank = RANKX(FILTER(ALL(Table1),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0)&&WEEKDAY([Date],2)<6),CALCULATE(MIN([Date])),,ASC)
2.modify your measure.
YTD =
VAR EightBusinessday =
MINX(FILTER(ALL(Table1),EOMONTH([Date],0)=EOMONTH(MAX('Table1'[Date]),0)&&[rank]=8),[Date])
VAR EndDate =
IF (
TODAY () < EightBusinessday,
EOMONTH ( TODAY (), -2 ),
EOMONTH ( TODAY (), -1 )
)
RETURN
CALCULATE (
FactLaborBudgetHourBV[M_BudgetLaborHour],
KEEPFILTERS ( DimDate[Date] <= EndDate )
)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
You could add another measure as rank Business day measure. then modify your meaure.
1. rank measure.
rank = RANKX(FILTER(ALL(Table1),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0)&&WEEKDAY([Date],2)<6),CALCULATE(MIN([Date])),,ASC)
2.modify your measure.
YTD =
VAR EightBusinessday =
MINX(FILTER(ALL(Table1),EOMONTH([Date],0)=EOMONTH(MAX('Table1'[Date]),0)&&[rank]=8),[Date])
VAR EndDate =
IF (
TODAY () < EightBusinessday,
EOMONTH ( TODAY (), -2 ),
EOMONTH ( TODAY (), -1 )
)
RETURN
CALCULATE (
FactLaborBudgetHourBV[M_BudgetLaborHour],
KEEPFILTERS ( DimDate[Date] <= EndDate )
)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You can create a measure with this kind of logic:
var _date = calculate(max(calendar[business day]),all(calendar),calendar[date]=today()) return
IF(_date<8,[measure with data until Novermeber],[measure with data until december])
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
The below was the query written, Kindly let me know ho we can change as per the logic
YTD =
VAR EightBusinessday =
CALCULATE (
MIN ( DimDate[Date] ),
DimDate[CurrentMonthBusinessDay] = 15
)
VAR EndDate =
IF (
TODAY () < EightBusinessday,
EOMONTH( TODAY (), -2 ),
EOMONTH ( TODAY (), -1 )
)
RETURN
CALCULATE (
FactLaborBudgetHourBV[M_BudgetLaborHour],
KEEPFILTERS( DimDate[Date] <= EndDate )
)
@Anonymous , Assume you have two measure like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Then you can have a measure
if(day(Today()) < 8 , [MTD Sales],[last MTD Sales])
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |