Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

YTD,MTD and QTD related month and year display

Hi,

I have given user selection for YTD,QTD and MTD.So when user selects any of these i need to display the related from Month year to To month Year.

Example : If user selects YTD and i have data from Jan'19 to Sep'19.So in a text object i need to display as (Jan'19 to Sep'19) If selected year is 2018 Then i need to display (Jan'18 to Dec'18). Same with QTD and MTD selections. Could anyone pls help me on this.

 

Thanks & Regards, Abhilash

1 ACCEPTED SOLUTION

So I believe that you want to say that the value/measure that you are calculating, for the last 3 months is 0.

So you can create measure as below:

Dynamic Range = 
VAR SelectedYear = YEAR(SELECTEDVALUE(Date[Year]))
VAR CurrentYearMonth = CALCULATE(FORMAT(LASTDATE(Date[Date]),"MMM"),FILTER(Table,[Measure]=0))
RETURN 
    IF(YEAR(TODAY())=SelectedYear,"Jan "&SelectedYear&" - "&CurrentYearMonth&" "&SelectedYear,"Jan "&SelectedYear&" - "&"Dec "&SelectedYear)

 

Please let me know if it didn't. 

View solution in original post

10 REPLIES 10
rajulshah
Super User
Super User

Hello @Anonymous,

 

Can you provide which filters are you going to have on your page?

Anonymous
Not applicable

Hi I will be giving Year as Fillter and Periodicity like YTD,QTD and MTD.So the user can select Year and Any of the periodicity.

So if the current year is selected, it should show period for the data present and then if any other year is selected, it should show Jan to Dec of that selected year.

Am I understanding it, right?

Anonymous
Not applicable

Yes I need to Display the Start Month And End month of the selected Year and Periodicity.

 

Like If i Select Year 2019 and Periodicity as YTD then i need to show a lable Mentioning as Jan'19-Sep'19

as i have data upto September for the selected Year.

 

If slected Year is 2018  it should show as Jan'18-Dec'18

You can create a measure as follows:

Dynamic Range = 
VAR SelectedYear = YEAR(SELECTEDVALUE(Date[Year]))
VAR CurrentYearMonth = FORMAT(LASTDATE(Date[Date]),"MMM")
RETURN 
    IF(YEAR(TODAY())=SelectedYear,"Jan "&SelectedYear&" - "&CurrentYearMonth&" "&SelectedYear,"Jan "&SelectedYear&" - "&"Dec "&SelectedYear)

Hope this helps. 

Anonymous
Not applicable

When i select 2018 it is showing as below.I wonder where is this 1905 coming from if i select any year it is showing the same as 1905.

Year.PNG

And for information in data provided i have dates upto Dec'19 whereas the last 3 months are Zeros so if i take directly in 2019 also the maximum year is Dec How to ignore this and consider Sep as month for current Year.

 

 

 

 
 
Anonymous
Not applicable

Ignore the above one as i resolved it to show proper year .

 

I need to resolve this.

 

"And for information in data provided i have dates upto Dec'19 whereas the last 3 months are Zeros so if i take directly in 2019 also the maximum year is Dec How to ignore this and consider Sep as month for current Year."

So I believe that you want to say that the value/measure that you are calculating, for the last 3 months is 0.

So you can create measure as below:

Dynamic Range = 
VAR SelectedYear = YEAR(SELECTEDVALUE(Date[Year]))
VAR CurrentYearMonth = CALCULATE(FORMAT(LASTDATE(Date[Date]),"MMM"),FILTER(Table,[Measure]=0))
RETURN 
    IF(YEAR(TODAY())=SelectedYear,"Jan "&SelectedYear&" - "&CurrentYearMonth&" "&SelectedYear,"Jan "&SelectedYear&" - "&"Dec "&SelectedYear)

 

Please let me know if it didn't. 

Anonymous
Not applicable

@rajulshah Thank You.It worked 😃

Hello @Anonymous,

Can you provide the DAX that you wrote? Also, it would be better if you provide sample data so that I can understand well.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.