Select Page

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 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([email protected] Between YearStartDate(CurrentDate()) and (CurrentDate()-1), Revenue, 0)){~+}

Previous YTD Total Cases >> Sum(IF([email protected] Between YearStartDate(CurrentDate()) and (CurrentDate()-365), Revenue, 0)){~+}

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

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