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...
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

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