Below document outlines the steps to perform transformation on the metric/KPI that you’re interested for the analysis based on a date column. This helps self service analysis to perform transformation easily within the dossier.

Step 01: Drag the Category attribute into Grid section

Step 02: Search for the metric that you are interested to perform YTD/MTD. Here I have taken “Revenue” & right click “Create Metric”

Step 03: Switch to “Formula” editor

Step 04: Use below formula/syntax to achieve transformation

Current YTD Syntax: Sum(IF([<Date Column>]ID Between YearStartDate(CurrentDate()) and (CurrentDate()-1), [<Metric Name>], 0)){~+}

Current MTD Syntax: Sum(IF([<Date Column>]ID Between MonthStartDate(CurrentDate()) and (CurrentDate()-1), [<Metric Name>], 0)){~+}

Current QTD Syntax: Sum(IF([<Date Column>]ID Between QuarterStartDate(CurrentDate()) and (CurrentDate()-1), [<Metric Name>], 0)){~+}

Previous YTD Syntax: Sum(IF([<Date Column>]ID Between YearStartDate(CurrentDate()-365) and (CurrentDate()-365), [<Metric Name>], 0)){~+}

Previous MTD Syntax: Sum(IF([<Date Column>]ID Between MonthStartDate(CurrentDate()-365) and (CurrentDate()-365), [<Metric Name>], 0)){~+}

Previous QTD Syntax: Sum(IF([<Date Column>]ID Between QuarterStartDate(CurrentDate()-365) and (CurrentDate()-365), [<Metric Name>], 0)){~+}

Example:

Current YTD Total Cases >> Sum(IF(Day@ID Between YearStartDate(CurrentDate()) and (CurrentDate()-1), Revenue, 0)){~+}

Previous YTD Total Cases >> Sum(IF(Day@ID Between YearStartDate(CurrentDate()) and (CurrentDate()-365), Revenue, 0)){~+}

Step 05: Drag and Drop the created metrics to the grid

Step 06: Enable “Total” if needed for additional analysis

By this way, you can easily achieve transformation within MicroStrategy using the functions itself.