Forum Discussion
SamePeriodLastYear for DateTime
hi
i am trying to get value for sameperiodlastyear using datetime column in date table.
i have column Datetime in table date and where the data is located transcationdate,no of transcation.
i want to drill down year,month,date,hours too.
Previous Year = CALCULATE(SUM('TransTable'[no.of trans]),SAMEPERIODLASTYEAR('DateTime'[DateTime]) )
this is not working as there are duplicates dates due to hours.
below is the same data and desired output.
| DateTime table | Transcation Table | Desired output | |||||||
| DateTime | Date | ID | TransDate | no. of trans | |||||
| 01/01/2020 01:00 | 01/01/2020 | 1 | 01/01/2020 01:00 | 10 | Currentyear | Last year | |||
| 01/01/2020 02:00 | 01/01/2020 | 2 | 01/01/2020 02:00 | 20 | 01/01/2020 01:00 | 10 | 15 | ||
| 01/01/2020 03:00 | 01/01/2020 | 3 | 01/01/2020 03:00 | 30 | 01/01/2020 02:00 | 20 | 30 | ||
| 01/01/2019 01:00 | 01/01/2019 | 4 | 01/01/2019 01:00 | 15 | 01/01/2020 03:00 | 30 | 40 | ||
| 01/01/2019 02:00 | 01/01/2019 | 5 | 01/01/2019 02:00 | 30 | |||||
| 01/01/2019 03:00 | 01/01/2019 | 6 | 01/01/2019 03:00 | 40 | |||||
Hi bideveloper555 ,
Time intelligence didn't work for datetime type, you can create a calculated column for lastyeardatetime and convert it to datetime type:
lastyeardate = var _date = DATE(YEAR(Transcation[TransDate])-1,MONTH(Transcation[TransDate]),DAY(Transcation[TransDate])) var time =FORMAT(Transcation[TransDate],"hh:mm:ss") return _date&" "&timeThen create the following measure:
Currentyear = SUM(Transcation[no. of trans]) Lastyear = CALCULATE([Currentyear],FILTER(ALL(Transcation),Transcation[TransDate] = MAX(Transcation[lastyeardate])))For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW3eGcCbj21LjU_e0HdgMWgBF-IT_SqqnAOukonDFg0GKQ?e=KZ2svI
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
3 Replies
bideveloper555 , this will not work with date time, You can only use date
Create a date column
Date = [DateTime].date
or
Date = date(year([DateTime ]),month([DateTime ]),day([DateTime ]))Join date with date of date table and then try
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 :radacad sqlbi My Video Series Appreciate your Kudos.
hi,
i created a date table and created date column in fact table.joined both date columns but still i cant drill down to HH.I can drill YY>Q>MMM>D but Hours.
when i marked Date table as Date,drill down doesnt work at all.
Hi bideveloper555 ,
Time intelligence didn't work for datetime type, you can create a calculated column for lastyeardatetime and convert it to datetime type:
lastyeardate = var _date = DATE(YEAR(Transcation[TransDate])-1,MONTH(Transcation[TransDate]),DAY(Transcation[TransDate])) var time =FORMAT(Transcation[TransDate],"hh:mm:ss") return _date&" "&timeThen create the following measure:
Currentyear = SUM(Transcation[no. of trans]) Lastyear = CALCULATE([Currentyear],FILTER(ALL(Transcation),Transcation[TransDate] = MAX(Transcation[lastyeardate])))For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW3eGcCbj21LjU_e0HdgMWgBF-IT_SqqnAOukonDFg0GKQ?e=KZ2svI
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
