Forum Discussion

bilalkhokar73's avatar
bilalkhokar73
Helper IV
5 years ago
Solved

CurrentMonthdate formula

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   ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    v-kkf-msft
    5 years ago

    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.

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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's avatar
    bilalkhokar73
    Helper IV
    5 years ago

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

     

    other formula not  accepting as asking date measures

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      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.

    • bilalkhokar73's avatar
      bilalkhokar73
      Helper IV
      5 years ago

      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's avatar
        v-kkf-msft
        Community Support
        5 years ago

        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.