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

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.