Forum Discussion
Power BI/ DAX
Experts,
Need to implement below scenarios in PowerBI. Please share your inputs. Thanks in advance.
A date column is available in the table/source query
MTD
If I select 2018-June,
Current Month 2018-June vs Prior year same month 2017-May
Example 2018-May vs 2017-May
QTD
[2018-Apr]+[2018-May]+[2018-June] versus [2017-Apr]+[2017-May]+[2017-June]
YTD
[2018-Jan] to [2018-June] versus [2017-Jan] to [2017-June]
Hi,
Try this
- Create a Calendar Table
- Create a relatiosnhip from the Date column of your base data table to the Date column of your Calendar Table
- In the Calendar Table, write the following calculated column formulas: YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
- Create a slicer for months and another one for Years. Drag Month and Year from the Calendar Table. Select any one Year and month
- Write these measures
Revenue=SUM(Data[Sales])
QTD revenue=CALCULATE([Revenue],DATESQTD(Calendar[Date])
YTD revenue=CALCULATE([Revenue],DATESYTD(Calendar[Date],"31/12")
Hope this helps.
1 Reply
Hi,
Try this
- Create a Calendar Table
- Create a relatiosnhip from the Date column of your base data table to the Date column of your Calendar Table
- In the Calendar Table, write the following calculated column formulas: YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
- Create a slicer for months and another one for Years. Drag Month and Year from the Calendar Table. Select any one Year and month
- Write these measures
Revenue=SUM(Data[Sales])
QTD revenue=CALCULATE([Revenue],DATESQTD(Calendar[Date])
YTD revenue=CALCULATE([Revenue],DATESYTD(Calendar[Date],"31/12")
Hope this helps.