Forum Discussion

siros92's avatar
siros92
Frequent Visitor
3 years ago
Solved

Help needed with month total

Hi all, thanks for all previous help. Love this community. 

 

I've got a bit of an interesting one i'm hoping you guys can help me with. 

I've got a calculated colum displaying total month income per customer and because of the datastructure i've been forced into having it like that - I know it is not ideal... 

I'm now trying to make a calculated column for a month total based on the sales per month column. 

Below is a mock sheet with some data and what would be the desired outcome. the desired outcome is a sum by month of the uniqe value per customer. 

   desired outcome
dateCustomer Sales per monthTotal sales month
03.01.2022xx150000450000
02.01.2022xx150000450000
01.01.2022xx150000450000
03.01.2022yy200000450000
02.01.2022yy200000450000
01.01.2022yy200000450000
03.01.2022zz100000450000
02.01.2022zz100000450000
03.02.2022xx50000180000
02.02.2022xx50000180000
01.02.2022xx50000180000
03.02.2022yy100000180000
02.02.2022yy100000180000
01.02.2022yy100000180000
03.02.2022zz30000180000
02.02.2022zz30000180000


in advance thanks for any help! 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated column.

     

    Total sales month CC =
VAR _currentmonthend =
    EOMONTH ( Data[date], 0 )
VAR _uniquetablepermonth =
    SUMMARIZE (
        FILTER ( Data, EOMONTH ( Data[date], 0 ) = _currentmonthend ),
        Data[Customer],
        Data[Sales per month]
    )
RETURN
    SUMX ( _uniquetablepermonth, Data[Sales per month] )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated column.

     

    Total sales month CC =
VAR _currentmonthend =
    EOMONTH ( Data[date], 0 )
VAR _uniquetablepermonth =
    SUMMARIZE (
        FILTER ( Data, EOMONTH ( Data[date], 0 ) = _currentmonthend ),
        Data[Customer],
        Data[Sales per month]
    )
RETURN
    SUMX ( _uniquetablepermonth, Data[Sales per month] )

     

  • siros92's avatar
    siros92
    Frequent Visitor
    3 years ago

    Thank you very much, you my friend are a propper star! 

    Again thank you as well for also taking the time to show it propperly it is greatly appreciated.