Matrix Query

[Sdhn420]

How can i show data in matrix from last day of previous year, last day of previous month in current year and last 3 days of current month. 

Example: (if today is 18.02.2021)

31.12.2019    -        31.12.2020   -   31.01.2021       - 15.02.2021          - 16.02.2021         - 17.02.2021

[v-jayw-msft · Accepted solution]

Hi @Sdhn420 ,

You will need to create several measures for these values.

measure1 = calculate([value],filter(table,date = date(year(today()),1,1) -1))

measure2 = calculate([value],filter(table,date = IF(MONTH(TODAY())=1,DATE(YEAR(TODAY()),1,1),date(year(today()),1,1) -1)))

measure3 = measure2 = calculate([value],filter(table,date <=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1&&date>DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-3))

Best Regards,

Jay

[amitchandak]

@Sdhn420 ,  Try a measure like

Measure =

var _d1 = date(year(today())-1,1,1) -1
var _d2 = date(year(today()),1,1) -1
var _d3 = eomonth(today(),0)
return
calculate([measure] , filter(Table,( Table[Date] = _d1 || Table[Date] = _d2 || Table[Date] = _d3) || (Table[Date] >=today() -2 && Table[Date] <=today()) ))

[Sdhn420]

How can I get last day of previous year?

[amitchandak]

@Sdhn420 ,

Measure =

var _d1 = date(year(today())-1,1,1) -1 //last to last year last day
var _d2 = date(year(today()),1,1) -1 //last day or previous year ; -1 from Jan 1
var _d3 = eomonth(today(),0) //This Month End
return
calculate([measure] , filter(Table,( Table[Date] = _d1 || Table[Date] = _d2 || Table[Date] = _d3) || (Table[Date] >=today() -2 && Table[Date] <=today()) ))