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
PAPutzback2
Helper II
Helper II

Table visual. How do show 1st row for the current YTD and 2nd row for previous YTD.

I have a date dimension linked to the table by date. The user has a slicer that lets them choose a quarter. Now they want to see a table with the top row showing the current YTD which works fine since that date is the slicer.

 

How do I get the second row to show for the same period of the previous year. I thoguht I might copy the table and link it to the calendar on Previous Year Last Date Of Quarter. Then stack the current year table on top to hide the column headers. But I don't like the fact that I will have to duplicate the data. I guess I could create a substitute date in the view which woudl duplicate the rows prior to ingest. But it seems like there might be an easier way. 
In my screenshot I have the two tables overlapped. The bottom table is showign the previous date that I want to see, but not the numbers for that year, as expected because I am still related on the current date.

PAPutzback2_0-1611271280594.png

Another option would be to create measures for PYTD to use in the second table, but it is still a hack. But it would less of a storage hit than duping the table.

 

 

1 ACCEPTED SOLUTION

Hi @PAPutzback2 ,

 

Sorry,I misunderstood your requirement. Pls modify the measure as below:

 

 

Measure = 
var _year=CALCULATE(YEAR(MAX('date table'[Date])),FILTER('date table','date table'[year&quarter] =SELECTEDVALUE('date table'[year&quarter])))
var _maxdate=CALCULATE(MAX('date table'[Date]),FILTER(ALL('date table'),'date table'[year&quarter] =SELECTEDVALUE('date table'[year&quarter])&&YEAR('date table'[Date]) = _year))
var _mindate=CALCULATE(MIN('date table'[Date]),FILTER(ALL('date table'),'date table'[year&quarter]=SELECTEDVALUE('date table'[year&quarter])&&YEAR('date table'[Date])=_year))
var _maxdate1=CALCULATE(MAX('date table'[Date]),FILTER(ALL('date table'),'date table'[Quarter]=RIGHT(SELECTEDVALUE('date table'[year&quarter]),2)&&YEAR('date table'[Date]) = _year-1))
var _mindate1=CALCULATE(MIN('date table'[Date]),FILTER(ALL('date table'),'date table'[Quarter]=RIGHT(SELECTEDVALUE('date table'[year&quarter]),2)&&YEAR('date table'[Date]) = _year-1))
Return
IF((MAX('Fact Table'[Period_Ending])>=_mindate&&MAX('Fact Table'[Period_Ending])<=_maxdate)||(MAX('Fact Table'[Period_Ending])>=_mindate1&&MAX('Fact Table'[Period_Ending])<=_maxdate1),SUM('Fact Table'[Budget]),BLANK())

 

 

And you will see:

Screenshot 2021-01-27 095133.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@PAPutzback2 , Typically when we create YTD and PYTD both will come with same date. 9/30/2020.

So are you selected 9/30/2020 and want two dates then you have try something like this iwth YTD

 

Try like example

 

Measure =
var _max = maxx(allselected(Date), Date[Date])
var _min = date(year(_max)-1, month(_max), day(_max))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31") , filter(all(Date), Date[Date] = _max || Date[Date] =_min ))


Measure =
var _max = maxx(allselected(Date), Date[Date])
var _min = date(year(_max)-1, month(_max), day(_max))
return
calculate(CALCULATE(SUM(Sales[Sales Amount]), filter(all(Date), Date[Date] = _max || Date[Date] =_min )),,DATESYTD('Date'[Date],"12/31") )

 

 

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.

I altered your first measure to match my date table as:

TST MEM 457 PYTD =
var _max = maxx(allselected('01 Reporting Period Calendar'[Date]), '01 Reporting Period Calendar'[Date])
var _min = date(year(_max)-1, month(_max), day(_max))
return
CALCULATE(SUM('Membership Counts'[457b]),DATESYTD('01 Reporting Period Calendar'[Date],"12/31") , filter(all('01 Reporting Period Calendar'[Date]), '01 Reporting Period Calendar'[Date] = _max || '01 Reporting Period Calendar'[Date] =_min ))
 
The measure does not return any values. I am also wondering why there are two measures. If I you two measures to a table, you get to columns. I want two rows one for current YTD and one for PYTD. 

I appreciate the help.

Hi  @PAPutzback2 ,

 

If possible,could you pls provide some sample data with expected output for test?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

I have a date table that goes from Jan 2019 through Dec 2020

My data table has the budget totals for the past two years.

PAPutzback2_0-1611590541657.png

I want to see the years on separate rows when I select  the current year quarter in the date slicer. I don't want to create a PYTD and YTD measures, because that would add columns

PAPutzback2_1-1611590672303.png

I want it to look like below. The only thing I can think of is to add a reporting Year quarter column to the SQL that provides the budget data and changing the date > Budget table relation to use that date. But that messes it up if I want to really see the one row for this quarter.

PAPutzback2_2-1611590806310.png

Is there a way to create a measure that would make two rows to show, like a group by in sql would.

 

 

Hi  @PAPutzback2 ,

 

First create a column in date table:

year&quarter = YEAR('date table'[Date])&" "&"Q"&QUARTER('date table'[Date])

Then create a  meausre as below:

Measure = 
var _year=CALCULATE(YEAR(MAX('date table'[Date])),FILTER('date table','date table'[year&quarter]=SELECTEDVALUE('date table'[year&quarter])))
var _maxdate=CALCULATE(MAX('date table'[Date]),FILTER(ALL('date table'),'date table'[year&quarter]=SELECTEDVALUE('date table'[year&quarter])&&YEAR('date table'[Date])=_year))
var _mindate=CALCULATE(MIN('date table'[Date]),FILTER(ALL('date table'),'date table'[year&quarter]=SELECTEDVALUE('date table'[year&quarter])&&YEAR('date table'[Date])=_year))
Return
IF(MAX('Fact Table'[Period_Ending])>=_mindate&&MAX('Fact Table'[Period_Ending])<=_maxdate,SUM('Fact Table'[Budget]),BLANK())

And you will see:

v-kelly-msft_0-1611642639137.png

 

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

That still only returns one row.

PAPutzback2_0-1611669445715.png

In order to see both years I have to select both quarters

PAPutzback2_1-1611669650899.png

 

Thank You!

Hi @PAPutzback2 ,

 

Sorry,I misunderstood your requirement. Pls modify the measure as below:

 

 

Measure = 
var _year=CALCULATE(YEAR(MAX('date table'[Date])),FILTER('date table','date table'[year&quarter] =SELECTEDVALUE('date table'[year&quarter])))
var _maxdate=CALCULATE(MAX('date table'[Date]),FILTER(ALL('date table'),'date table'[year&quarter] =SELECTEDVALUE('date table'[year&quarter])&&YEAR('date table'[Date]) = _year))
var _mindate=CALCULATE(MIN('date table'[Date]),FILTER(ALL('date table'),'date table'[year&quarter]=SELECTEDVALUE('date table'[year&quarter])&&YEAR('date table'[Date])=_year))
var _maxdate1=CALCULATE(MAX('date table'[Date]),FILTER(ALL('date table'),'date table'[Quarter]=RIGHT(SELECTEDVALUE('date table'[year&quarter]),2)&&YEAR('date table'[Date]) = _year-1))
var _mindate1=CALCULATE(MIN('date table'[Date]),FILTER(ALL('date table'),'date table'[Quarter]=RIGHT(SELECTEDVALUE('date table'[year&quarter]),2)&&YEAR('date table'[Date]) = _year-1))
Return
IF((MAX('Fact Table'[Period_Ending])>=_mindate&&MAX('Fact Table'[Period_Ending])<=_maxdate)||(MAX('Fact Table'[Period_Ending])>=_mindate1&&MAX('Fact Table'[Period_Ending])<=_maxdate1),SUM('Fact Table'[Budget]),BLANK())

 

 

And you will see:

Screenshot 2021-01-27 095133.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

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.