Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
Solved! Go to 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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@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:
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.
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
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.
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:
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.
In order to see both years I have to select both quarters
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |