Creating flags for InMTD and InQTD

[y_grynechko]

Hi,

I am trying to figure out how to get data for MTD and QTD dynamically. I have this flags created: 

If(DayNumberOfYear(D) <= DayNumberOfYear($(v_Today)), 1, 0 ) as IsInYTD,
If(DayNumberOfQuarter(D) <= DayNumberOfQuarter($(v_Today)), 1, 0) as IsInQTD,
If(Day(D) <= Day($(v_Today)), 1, 0) as IsInMTD

YTD works fine, when I am doing comparison of the year to date sales to last year to date sales:

Sum( { $<[IsInYTD]={1}, Year ={"$(=Max(Year))"},Month=> } Sales ) - Sum( { $<[IsInYTD]={1}, Year ={"$(=Max(Year)-1)"},Month=> } Sales )

But the same logic doest work for MTD and QTD. It just gives me whole year's sales.

Sum( { <[IsInMTD]= {1}, Year ={"$(=Max(Year))"}> } [Voucher USD Amount] ).

What I needs is to be able to compare data from same periods. For example:

Today is 1/10/2019

When I select year 2018 I want to compare data from 1/1/2018 to 1/10/2018 with data from 1/1/2017 to 1/10/2017

Thanks!

[sunny_talwar]

Try this

If(DayNumberOfQuarter(D) <= DayNumberOfQuarter($(v_Today)) and QuarterStart(D) = QuarterStart($(v_Today)), 1, 0) as IsInQTD,
If(Day(D) <= Day($(v_Today)) and MonthStart(D) = MonthStart($(v_Today)), 1, 0) as IsInMTD

[y_grynechko]

Sunny,

thank you for the reply but it works only for the current year. When I select any other year the values go to 0. 

[sunny_talwar]

Are you looking to flag each year based on today's month, or quarter?

All of these will be flagged with 1

01/01/2016-01/11/2016
01/01/2017-01/11/2017
01/01/2018-01/11/2018

But this isn't based on any other selection, right? So, let's say you select 1st Nov, would you want the quarter flag to change to

09/01/2016-11/01/2016
09/01/2017-11/01/2017
09/01/2018-11/01/2018

This is not what you want, right?

[y_grynechko]

Today is 1/11/2019 So after opening the app I will have 2 KPIs. One to show QTD sum of sales: from 1/1/2019 to 1/11/2019

and the other KPI to show sum of sales 1/1/2018 to 1/11/2018.

And this is easy to do but what is need is for it to change when user select different year. For example if user select's year 2017 KPI one will show sum of sales: from 1/1/2017 to 1/11/2017 

and the other KPI to show sum of sales 1/1/2016 to 1/11/2016.

Data looks like this:

[sunny_talwar · Accepted solution]

Then in that case, try this

If(DayNumberOfQuarter(D) <= DayNumberOfQuarter(Today()) and Ceil(Month(D)/3) = Ceil(Month(Today())/3), 1, 0) as IsInQTD,
If(Day(D) <= Day(Today()) and Month(D) = Month(Today()), 1, 0) as IsInMTD

But now, make sure to use set analysis on Year field as well

For Max year

{<Year = {"$(=Max(Year))"}, IsInMTD = {'1'}>}

For Max year minus 1

{<Year = {"$(=Max(Year)-1)"}, IsInMTD = {'1'}>}

[y_grynechko]

It works, thank you Sunny!