In Desktop, I have the (pictured) setup, and want to change the columns to the following, for example if my latest data is through July:
July 2018 July 2019 YTD 2018 YTD 2019
Is this possible? I don't mind manually configuring July as the current month, ie I don't need some kind of complex logic that figures out what the latest month is from the data, but would of course be open to knowing if that's possible as well.
Thanks
I don't think there's a way to do this without using Measure Names and Measure Values for the month/YTD values. But you're already using those for your measures. If you can pivot your measures (see https://www.kenflerlage.com/2018/06/pivoting.html), then this should be doable. Here's an example using superstore. I've pivoted the measures, Sales, Quantity, Profit, and Discount. Here's a view similar to yours.
Now I'll create a parameter for the "as of" date.
Now we'll create separate measures for each of the periods you're looking for:
Monthly - Current Year
// The measure for the current year and month
IF YEAR([Order Date]) = YEAR([As Of Date]) AND MONTH([Order Date]) = MONTH([As Of Date]) THEN
[Value]
END
Monthly - Previous Year
// The measure for the previous year and month
IF YEAR([Order Date]) = YEAR([As Of Date])-1 AND MONTH([Order Date]) = MONTH([As Of Date]) THEN
[Value]
END
YTD - Current Year
// The measure for the current year YTD
IF YEAR([Order Date]) = YEAR([As Of Date]) AND MONTH([Order Date]) <= MONTH([As Of Date]) THEN
[Value]
END
YTD - Previous Year
// The measure for the previous year YTD
IF YEAR([Order Date]) = YEAR([As Of Date])-1 AND MONTH([Order Date]) <= MONTH([As Of Date]) THEN
[Value]
END
Then use Measure Names and Measure Values to display these as shown below:
See attached.
