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.
Thsi is my current month till date formula, thsi is giving me result till yeterday date for full month
let say today's date is 25june2021 so thsi formula gives me result from 01june to 24 june
Solved! Go to Solution.
@bilalkhokar73 , second formula is assuming you have a date table . I saw ALL(date) in your formula, thought that you have date table.
If not please create a date table and use that
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi @bilalkhokar73 ,
Try the following formula:
Current_Month =
CALCULATE(
SUM(salesT[amt]),
FILTER(
ALL('date'),
'date'[Date] >= EOMONTH( TODAY(), -1 ) + 1
&& 'date'[Date] <= EOMONTH( TODAY(), 0 )
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @bilalkhokar73 ,
Try the following formula:
Current_Month =
var FirstDay =
CALCULATE(
SUM(salesT[amt]),
FILTER(
ALL(salesT),
'salesT'[date] >= EOMONTH( MAX(salesT[date]), -2 ) + 1
&& 'salesT'[date] <= EOMONTH( MAX(salesT[date]), -1 )
)
)
var OtherDays =
CALCULATE(
SUM(salesT[amt]),
FILTER(
ALL(salesT),
'salesT'[date] >= EOMONTH( MAX(salesT[date]), -1 ) + 1
&& 'salesT'[date] < MAX(salesT[date])
)
)
return
IF(
MAX(salesT[date]) = EOMONTH( MAX(salesT[date]), -1 ) + 1,
FirstDay,
OtherDays
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@bilalkhokar73 , Try if datesmtd can work with -1 day
CALCULATE(SUM(salesT(amt)), datesmtd(dateadd(Date[date],-1,day))
or try like
Current_Month =
var _max = max(allselected(Date), Date[date]) - 1
var _min = eomonth(_max,-1)+1
return
CALCULATE(SUM(salesT(amt)),FILTER(ALL(date),date[Date]>=_min && date[Date] <=_max))
CALCULATE(SUM(salesT(amt)), datesmtd(dateadd(Date[date],-1,day)) giving blank result
other formula not accepting as asking date measures
i have date table
thsi giving an error max(allselected(Date), Date[date]) - 1 , after all select date table column shoudl coem
Hi @bilalkhokar73 ,
Try the following formula:
Current_Month =
CALCULATE(
SUM(salesT[amt]),
FILTER(
ALL('date'),
'date'[Date] >= EOMONTH( TODAY(), -1 ) + 1
&& 'date'[Date] <= EOMONTH( TODAY(), 0 )
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It is working, but the proper result I can give you only on 1 July as on 1 July I hope data will come for full June month and on 2 July it will come for only 1 July like that, my reports I never count live sales, all sales are till yesterday's date @v-kkf-msft
Hi @bilalkhokar73 ,
Try the following formula:
Current_Month =
var FirstDay =
CALCULATE(
SUM(salesT[amt]),
FILTER(
ALL(salesT),
'salesT'[date] >= EOMONTH( MAX(salesT[date]), -2 ) + 1
&& 'salesT'[date] <= EOMONTH( MAX(salesT[date]), -1 )
)
)
var OtherDays =
CALCULATE(
SUM(salesT[amt]),
FILTER(
ALL(salesT),
'salesT'[date] >= EOMONTH( MAX(salesT[date]), -1 ) + 1
&& 'salesT'[date] < MAX(salesT[date])
)
)
return
IF(
MAX(salesT[date]) = EOMONTH( MAX(salesT[date]), -1 ) + 1,
FirstDay,
OtherDays
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@bilalkhokar73 , second formula is assuming you have a date table . I saw ALL(date) in your formula, thought that you have date table.
If not please create a date table and use that
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |