Forum Discussion

erhan_79's avatar
erhan_79
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

need formula

Hi there ;

 

i need your help to create below formula 

 

i have a table as below , calculating rule will be : for each actual month ( acual month is august today) , system will find first day and will sum the first day's amounts

 

for  below example , we are in August , on the table first days for august seems 04.08.2020 , so system will sum 500+600 = 1100 

that days and and actual month sure can change for every month but rule same = first day of month and sum all the first fays for actual months always.

 

i just need a measure

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi erhan_79 ,

    According to my understanding, you want to calculate the sum of the first day’s(in table column) amount of current month , right?

    You could use the following formula:

    sum =
VAR _first =
    CALCULATE (
        MIN ( 'SumFirstDayMonth'[Date] ),
        FILTER ( ALL ( SumFirstDayMonth ), 'SumFirstDayMonth'[Status] = "Actual Month" )
    )
RETURN
    CALCULATE (
        SUM ( SumFirstDayMonth[Amount] ),
        FILTER ( ALL ( SumFirstDayMonth ), 'SumFirstDayMonth'[Date] = _first )
)

    My visualization looks like this:

    Is the result what you want? If not, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi erhan_79 

     

    _sum = CALCULATE(SUM([Amount]),[date]=CALCUALTE(MIN([date]),MONTH([date])=MONTH(TODAY())))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi erhan_79 ,

    According to my understanding, you want to calculate the sum of the first day’s(in table column) amount of current month , right?

    You could use the following formula:

    sum =
VAR _first =
    CALCULATE (
        MIN ( 'SumFirstDayMonth'[Date] ),
        FILTER ( ALL ( SumFirstDayMonth ), 'SumFirstDayMonth'[Status] = "Actual Month" )
    )
RETURN
    CALCULATE (
        SUM ( SumFirstDayMonth[Amount] ),
        FILTER ( ALL ( SumFirstDayMonth ), 'SumFirstDayMonth'[Date] = _first )
)

    My visualization looks like this:

    Is the result what you want? If not, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      6 years ago
      erhan_79 if Anonymous 's solution works for you please mark as a solution.

      I am guessing however that you do not actually have a 'current month' 'previous month' status column in your data table, so if that is the case, please provide sample data table of what your data looks like when in the Power BI data model.

      We can use DAX to find the current month using the TODAY() function, and take that value to find the MIN() or FIRSTDATE() in your Fact or transaction table for the MONTH(TODAY())

      Just let us know what columns you have and we'll help you out.
    • erhan_79's avatar
      erhan_79
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Thank you very much Anonymous  ;

       

      it is working perfect 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    erhan_79 , better to do it with month year, try a new column like

     

    New Status =
    var _min = minx(filter(Table, format([Date], "YYYYMM") =format( earlier([Date]), "YYYYMM") && eomonth(today(),0) = eomonth([Date],0)),[Date])
    return
    if([Date]=_min, "First Date of Month",[Status])

     

    or a new measure

     

    New Status =
    var _min = minx(filter(Table, format(Table[Date], "YYYYMM") =format( max(Table[Date]), "YYYYMM") && eomonth(today(),0) = eomonth([Date],0)),[Date])
    return
    if([Date]=_min, "First Date of Month",[Status])

     

    better to have Month year column and use that, to compare months

    Month year = format(Table[Date], "YYYYMM")

     

    example

    New Status =
    var _min = minx(filter(Table, ([Month Year]) =format( earlier([Month Year])) && eomonth(today(),0) = eomonth([Date],0)),[Date])
    return
    if([Date]=_min, "First Date of Month",[Status])

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    Hi erhan_79 ,

    Here is my take on this:

    First create a calculated column to group your data by Year and  Month

    Month & Year = 
FORMAT ( 'Table'[Date], "YYYYMM" )

     

    Second, create a measure to determine the first day with data  in the current month.

    First Day Current Month = 
VAR __START =
    EOMONTH ( TODAY (), -1 ) + 1
VAR __END =
    EOMONTH ( TODAY (), 0 )
RETURN
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Month & Year] ),
            'Table'[Date] >= __START
                && 'Table'[Date] <= __END
        )
    )

     

    And lastly, create a measure to sum rows with dates equal to the first day of the current month with data

    First Day Total = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER ( 'Table', 'Table'[Date] = [First Day Current Month] )
)