Creating YTD function in the script

[iczkla]

Hello,

I have a column in my table, which is called 'Transaction Effective Moment'. It is in DD/MM/YYYY format.

In there I have dates from 01/01/2019 till 31/12/2021

Each month I am reporting transactions for the previous one.

So now we are in August, I need to report the number of transactions which taken place till the end of July (31/07/2021).

I was wondering whether it is possible to create an expression in script which will display only transactions that took place between 01/01/2021 and end of previous month, so that for example in August these will be transactions from 01/01/2021 till 31/07/2021, in September from 01/01/2021 till 31/08/2021 and so on.

Any help will be much appreciated.

Thank you!!

[Andrei_Cusnir]

If my understanding is correct, you would like to create a table that will show you transactions only if the date is after (or equal) the first day of the current year and before (or equal) the last day of previous month. In this case, you will need to combine a couple of expressions logic:

1. We need to get the first day of the current year: YearStart( Today() ) this will give us e.g. 1/1/2019, 1/1/2020, 1/1/2021 depending on our current year
2. We need to get the last day of the previous month: MonthEnd( AddMonths( Today(), -1 ) ) 
   1. AddMonths(Today(), -1) will take the current date and will add -1 month, which will give us the pervious month of current month
   2. MonthEnd() will give us the last day of the date inside
3. Now combine everything together: =If(Date >= YearStart(Today()) and Date<= MonthEnd(AddMonths(Today(), -1)), <Field>)  will show us only the field where the criteria is met, otherwise it will show us null. 

To test this solution, lets assume that we have the following dataset:

As you can see, it contains transaction record from 1st January 2020 until 15th August 2021. Since today we have August 2021, based on your criteria, you want to display only the records from 1st January 2021 to 11th July 2021 (Because we don't have transactions between 8th July 2021 and 31st July 2021).

If we modify the "Amount" dimension and replace it with the expression mentioned above "=If(Date >= YearStart(Today()) and Date<= MonthEnd(AddMonths(Today(), -1)), Amount)", we will get:

As you can see the Amount is only visible for transaction dates grater or equal to 1/1/2021 and less or equal to 31/7/2021.

Now you can uncheck the "Include null values":

And the result is:

I hope that this information was helpful!

[iczkla]

Hi Andrei! Thank you so much, this solution definitely provides correct output when used in the table. 

I was wondering how could I use it for example in chart or KPI? So that only values that are in the table are shown?

[Andrei_Cusnir]

Hello! 

I am glad to hear that my solution provided the outcome that you were looking for! For the other charts that you have mentioned, the solution is actually very similar. For example:

1. KPI chart: You can use the measure =Count( If(Date >= YearStart(Today()) and Date<= MonthEnd(AddMonths(Today(), -1)), Amount) ), where the output is 16 in this case. As you can see from the first screenshot above, we have many more records and when the expression is applied, only 16 of them are shown:  
2. Now for the other charts, e.g. Bar chart, I have added If(Date >= YearStart(Today()) and Date<= MonthEnd(AddMonths(Today(), -1)), Date) as Dimension, where I have replaced the Amount with Date so that only the dates that I want to see will appear. Then for the Measure, I have just added =Amount in the expression and this is the result:  
   1. As you can see, only the 16 records that we need are displayed.

I hope that this information was helpful for you, please don't forget to mark an answer as solution if it was helpful, as it will give the post more visibility and will allow other community members to find the right answers as well !  

[iczkla]

Thank you again for the fast response! The solution indeed works, I just have one more question: how can I incorporate this into measure with more conditions? Let's say I have expression:

Count({[Action Type]={'Internal'} [Event])  and I would like to input all internal events in the timeframe we determined in our previous expression??

I tried below but it does not work:

Count(if(Date >= YearStart(Today()) and Date<= MonthEnd(AddMonths(Today(), -1)), Amount) ) {[Action Type]={'Internal'} [Event])) 

[Andrei_Cusnir · Accepted solution]

Hello,

Thanks for letting me know again that the solution worked for you. I believe that for your latest question there is a different solution and you have to do this with set analysis instead of using the expression that we had for the Table charts etc.

I have added another field Action Type in my data filed and I have randomly assigned the value "Internal" and "External" to have the same use case scenario as yours. To make sure that the data is clearly visible (just for taking an example) I have presented it as it is:

As you can see for the date range that you have there are only 5 records where Action Type is set to "Internal". However if we count all the records in the data set that have Action Type filed set to "Internal", we can see that there are 10 of them:

To resolve this, we need to apply the following set analysis in the Measure expression:

=Count({<[Action Type]={'Internal'}>*<Date={">=$(=YearStart(Today()))<=$(=MonthEnd(AddMonths(Today(),-1)))"}>} Amount)

You can break this into the following parts:

• <[Action Type]={'Internal'}> Will ensure that we filter all the records with Action Type "Internal"
• " * " Will ensure that we take the data that belongs to both of the two set operands
• <Date={">=$(=YearStart(Today()))<=$(=MonthEnd(AddMonths(Today(),-1)))"}> Will take the data based on the date range that we have specified

This will give us the following KPI chart:

As you can see it gave us the number 5, which corresponds to the number of records that have Action Type "Internal" and belong to the dates range we have specified. For your use case scenario, don't forget to change the "Amount" field to "Events". Further information on how to use the Set analysis and set expressions can be found here.

I hope that this information has helped you resolve the issue that you are having. As I have also mentioned previously, please mark as solution offered all the answers that helped you resolve your issue. This will ensure that all community members will be able to find these solutions in case it is needed and will give more visibility to the post.