Simple aggregation

[Anonymous]

So I have a very basic table

Date    Category  

1/1/18  A

1/1/18  B

1/1/18  A

1/2/18  A

1/2/18  D

2/4/18  E

and so on

I can get the count of cetegory by Year Month rolled up, but I want the sigle row of just previous month dynamically. Meaning if I'm in Aug, it shows only July data

Something like

Year Month  --------  Count

07-2018      ---------- 50

Thanks in advance

[Anonymous]

Assuming you have the date table in your model:

CALCULATE(COUNT(Table1.Category),DATEADD(Dates[Date],-1,MONTH)

This will alwasy give you the last month count.

Thanks
Raj

[Anonymous]

No there is no data tbale in teh model, it is very flat csv file, all the data in a singel sheet.

[Seward12533 · Accepted solution]

I'd stil add the date table. Opens up all kinds of options. you can do this dynamically

Using Create Table form the modeling window

DateDim = CALENDAR(MIN(table[date]),MAX(table(date))

Link to your date column and Voila!

[Anonymous]

Can you try this?

CALCULATE(COUNT(Table1.Category),
                       YEAR( Table1[Date]) = YEAR(EOMONTH(TODAY(),-1)) && MONTH( Table1[Date]) = MONTH(EOMONTH(TODAY(),-1))
            ) 

[Anonymous]

Wouldn't

YEAR( Table1[Date]) = YEAR(EOMONTH(TODAY(),-1)) 

this give me previous year? I'm looking for current year but previous month.

[Anonymous]

No, This gives the Year of previous month.

[Seward12533]

Try this measure if you want to apply filters and have it show the last month of the filtered months.

Last Month Measure = CALCUALTE([MEASURE],FILTER(Date,date[Date]=MAX(date[Date]))

This one if you want the last month regardless of any filterss (for date) applied.

Last Month Measure = CALCUALTE([MEASURE],ALL(Date]),FILTER(ALL(Date),date[Date]=MAX(date[Date])

You can then create table and drag in your YEAR-Month form the DATE Table and then add your Last Month Measure.