Adding a calculated column to display monthly totals

[Anonymous]

Hi there,

I have the data as seen below but I would like to add 2 columns to it, 1 is the first of each month that is in the data and the second is the total for the month of each license per site.

In the example below, the extra columns for july would be 01/07/21 for the column to show the first of the month and for example Houdini Core for Sydney the monthly maximum would be 4.

Just not sure how to construct the Dax to come up with the first of each month then the sum of the site and different monthly maximum for the licenses.

I know this is more than likely fundamental and I have been looking at tutorials, Dax just isn't happening for me.

Thank you.

[ryan_mayu]

@Anonymous 

1. you can try this

column=date(year('date),month('date'),1)

2. not sure if i understand your request clearly.

coumn=calculate(sum(count),allexcept(table,site,liscence,column)

[Anonymous]

Hey there, 

the first one worked so now I see the first of each month for the weeks of those months in a new column, but the second one didn't.

I have tried this

MonthlyTotal = calculate(sum(count),allexcept(tblLicenseExpiry,tblLicenseExpiry[Site],tblLicenseExpiry[License],tblLicenseExpiry[Current]))

But keep getting syntax errors relating to the bracket?

[Anonymous]

My mistake there was a typo in what I was doing.   Also, apologies I worded the second part of the question completely wrong.  The new 'MonthlyTotal' column should be the lowest value for the month for a site and piece of software.

In this example the monthly total for July for Sydney Houdini FX would be 16 as that is the lowest value in the 'Count' column for that product for the month of July.  Sorry I was getting my requests mucked up.  

So the second new column is the lowest number for the month for the combo of Site and License based on checking against Count.

[ryan_mayu · Accepted solution]

@Anonymous 

try this

MonthlyTotal = calculate(min(count),allexcept(tblLicenseExpiry,tblLicenseExpiry[Site],tblLicenseExpiry[License]))

[ryan_mayu]

@Anonymous 

calculate （sum(tblLicenseExpiry[count]),XXXX

count is the column name in your table.