need formula

[erhan_79]

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

[danextian]

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

[amitchandak]

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

[Anonymous · Accepted solution]

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

[erhan_79]

Thank you very much @Anonymous  ;

it is working perfect 

[AllisonKennedy]

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

[Anonymous]

Hi @erhan_79 

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