CurrentMonthdate formula

[bilalkhokar73]

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

Current_Month = CALCULATE(SUM(salesT(amt)),FILTER(ALL(date),tm_date[Date]>=Date(YEAR(TODAY()),MONTH(TODAY()),1) && date[Date]<TODAY()))

Problem is here that when new month comes let say when 1st july will come then this formula show me blank becasue I calculate sales till yesterday so when 1july2021 will come then it shoudl shwo me sales of 01juen2021 to 30june2021 and when date is 02 july2021 then it is shwoing right result but my Aim is that thsi formula should show me sales of  last full month

[amitchandak · Accepted 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.

[v-kkf-msft · Accepted solution]

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.

[amitchandak]

@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))

[bilalkhokar73]

CALCULATE(SUM(salesT(amt)), datesmtd(dateadd(Date[date],-1,day)) giving blank result

other formula not  accepting as asking date measures

[bilalkhokar73]

i have date table 
thsi giving an error max(allselected(Date), Date[date]) - 1 , after all select date table column shoudl coem

[v-kkf-msft · Accepted solution]

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.

[bilalkhokar73]

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 

[v-kkf-msft · Accepted solution]

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.

[amitchandak · Accepted 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.