Aggregate daily input to monthly/yearly report

[tonyclifton]

Hello community,

I have a table with daily information about the licenses users have:

2020-02-21	License 1	userA
2020-02-21	License 2	userA
2020-02-20	License 1	userA
2020-02-20	License 2	userA
2020-02-21	License 1	userB
2020-02-20	License 1	userB

...

In PowerBI I linked the date column with my calendar.

The goal is to select the month or year column from the calendar to give me the count of licenses a user has for that selection.

So, from above example table I would like to see the following results by being able to select the Month or Year column:

Currently I can only use the date column with this formula but selecting a day is not suitable for a monthly overview:

LicensesCount =
CALCULATE (
COUNT(Table[License] );
ALLSELECTED( Table[Date]) )

I hope you can show me a way how to achieve this.
Thank you very much in advance.

[v-eachen-msft]

Hi @tonyclifton ,

You could use “Split” feature to split date as year, month and day.

Then you could put user column in the field pane and set as count.

[Baskar]

Dear Friend, 

 Try this as well,

Step 3Step 1Step 2

Here simple DAX is enough : Countrows of table.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

http://powerbi.baskarbiconsultant.com/

[vivran22]

Hello @tonyclifton,

If you have created additional support columns in your date table and it is linked with your data table, then you will not need any measure to get the required visual:

Calendar table:

Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Year", YEAR ( [Date] )
)

Month = STARTOFMONTH ('Calendar'[Date] )

Relationship:

Result:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[amitchandak · Accepted solution]

Create a date calendar table.

Month = format(Date[Date],"YYYY-MM")

Year= format(Date[Date],"YYYY")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

you can use count of a column or countrows(Table)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin