DAX for Earliest date and PY date in same column

[danielhough]

Hey guys! I need to create table which only has two rows, two columns. Fist column says, "vs Prior Year" and the second column are the two dates: Current Date, (Current date - 365). IE : 

vs Prior Year  |  Date

vs Prior Year  | 9/1/2018

vs Prior Year  | 9/1/2019

These dates are derrived from the table : 'HFM_Data_QTD(5 quarters)_102319'

Is this even possible?

Thanks!!!

[v-juanli-msft]

Hi @danielhough 

Would you like to show a table as below:

If so, create measures

current sales = SUM('Table'[sales])

Prior Year's sales = CALCULATE(SUM('Table'[sales]),SAMEPERIODLASTYEAR('date'[Date]))

difference = [current sales]-[Prior Year's sales]

If not, please let me know more details.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danielhough]

Thanks for your help Maggie, I would like the table to look just like this:

vs Prior Period  |  Date

vs Prior Year     |   9/1/2018

vs Prior Year     |   9/1/2019

vs Prior Qtr       |   9/1/2019

vs Prior Qtr       |   6/1/2019

This will allow me to have a two button Chiclet slicer to filter the data by PQ or PY, but I need the dates to be dynamicly calculated as new data is refreshed. This works at them moment in static form. 

[v-juanli-msft]

Hi @danielhough 

Sorry, i don't understand how to get this result.

Current date is 2019/12/17, but your date is 2019/9/1,

Could you tell me more about the rule of your table?

Best Regards
Maggie

[danielhough]

Sure @v-juanli-msft, the current date, is today's date, but the data in the table is of the last quarter. Its financial data, loaded quarterly and I need to create a slicer which selects the date ranges for comparison. 

Thanks!