How to get MTD ,QTD and YTD value using dynamic pr...

SriGaG1 · ‎09-13-2022

Hi There Experts.

I am new to power BI ans still learning on how to use parameters to create a date range picker and to that I need to use these time intelligence functions. . So I did a research and first what I dis was,

Step 1 - Creating a custom date table using the below calc

Date = CALENDAR(DATE(2019,01,01),TODAY())

Step 2 - Creating another table with time intelligence functions

Date Period =

union(ADDCOLUMNS(DATESMTD('Date'[Date]),"Type","MTD", "Order","1"),

ADDCOLUMNS(DATESQTD('Date'[Date]),"Type","QTD", "Order","2"),

ADDCOLUMNS(DATESYTD('Date'[Date]),"Type","YTD", "Order","3"),

ADDCOLUMNS(CALENDAR(DATE(2019,01,01),TODAY()),"Type","All Time", "Order","4"))

And based on the above, i have created the slicer like this.

The above date field shows the max date which is today. So when I clicked MTD on the above slicer, It works. All the details from Sep 1 - 13 will be taken and when I selected Sep 5, It is taking Sep 1 to Sep 5 data. So that is perfectly working. So the issue arives when we selecting an older date. Lets say, 29th of August, entire visualization will get blank. But the output need to be 1st of Aug -29th Aug. And qtd ,YTD values will change. and MTD button on the above pane will get dissapeared.

MTD selection

When selected an older date, MTD value will get dissapeared from the slicer. When we select 2021, Dec date, all MTD, QTD and YTD icons in the slicer will get dissapered.

How to solve this issue? I need to use a separate table like above for time intelligence functions. And using the above date slicer is a must. Is there a way to solve this out please?

Hope you have understood my issue. Can anyone help me on this please?

v-mengzhu-msft · ‎09-15-2022

Hi @SriGaG1 ,

Perhaps I didn't understand your question very well. What do you want to achieve?
Can you send me a sample data or a pbix file without private data? Also, tell me what you want to achieve.

Best regards,

Community Support Team Selina zhu

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