How to sumifs with DAX in power bi

[Figueiredo_Luis]

Dear all,

I need help with DAX to bring sales value in to my calendar.
The first table [Sales-dailyUpdate] I have two columns, one is the [Sale amount] and another is the [Activation date], the other table is the [Calendar] which I have the [Date] column.

I am strugling to bring those sales into the calendar dates populating the total sum from my [Sales-dailyUpdate] table  to a single date from the [Calendar] date.

Please help me with some ideas or solution.

Thanks in advance,

Figueiredo Luis

[DOLEARY85 · Accepted solution]

Sorry, based on your initial description I assumed you already had them calculated in the sales table.

try this:

Column = CALCULATE(SUM('Sales-dailyUpdate'[Sale amount]),FILTER('Sales-dailyUpdate','Sales-dailyUpdate'[Activation date]=VALUE('Calendar'[Date])))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Figueiredo_Luis]

I appreciate you support DOLEARY85.

[DOLEARY85]

No problem, happy to help 🙂

[Adamboer]

You can use a measure in DAX to calculate the total sales amount for each date in your calendar table based on the sales data in your [Sales-dailyUpdate] table.

Here's an example measure you can use:

Total Sales = SUM('Sales-dailyUpdate'[Sale amount])

To display this measure in your calendar table, create a relationship between your [Calendar] table and [Sales-dailyUpdate] table using the [Activation date] and [Date] columns respectively. Then, add the Total Sales measure to your calendar table as a new column. The measure will automatically aggregate the sales amounts for each date in the [Sales-dailyUpdate] table and display the total sales amount for each date in the calendar table.

Here's an example DAX formula to create a new column in your [Calendar] table to show the total sales amount for each date:

Total Sales = CALCULATE([Total Sales], USERELATIONSHIP('Sales-dailyUpdate'[Activation date], 'Calendar'[Date]))

This formula uses the CALCULATE function to calculate the Total Sales measure for each date in the [Calendar] table, using the relationship between the [Sales-dailyUpdate] table and [Calendar] table.

Once you add this formula to a new column in your [Calendar] table, you will see the total sales amount for each date in your calendar.

[DOLEARY85]

Hi,

you can create a calculated column in the date table and use lookupvalue to bring it through:

Column = LOOKUPVALUE('Sales-dailyUpdate'[Sale amount],'Sales-dailyUpdate'[Activation date],'Calendar'[Date])

I usually wouldn't recommend bringing anything other than date hierachy fields into the calendar table and just creating a relationship to the other tables.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Figueiredo_Luis]

Hi,

I don't think LOOKUPVALUE could work cos on the Sales-dailyUpdate we can find more than one sale in the same day while in calendar there is only one day.

[Figueiredo_Luis]

In other words LOOKUPVALUE is expected to return a single value.

[DOLEARY85 · Accepted solution]

Sorry, based on your initial description I assumed you already had them calculated in the sales table.

try this:

Column = CALCULATE(SUM('Sales-dailyUpdate'[Sale amount]),FILTER('Sales-dailyUpdate','Sales-dailyUpdate'[Activation date]=VALUE('Calendar'[Date])))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[pbix1 · Accepted solution]

Pretty much the same thing but in a different way, I use the below methodology. Then to get the value in a visual you can return the max value of the SalesAmount for the GroupKey. (There will only be one value, so MAX will return the actual value for the group). This is a generic example that could be adapted, but I thought it might be useful. Concat is a column that you want to group by, that could be a concatenation of other columns.

Column = VAR

        GroupKey = [Concat]

    RETURN

        CALCULATE(SUM([SalesAmount]), FILTER('Table1', [Concat] = GroupKey))