Monthly Trend for CountRows

[damit23183]

Hi,

I am working on one calculation where I need calcualte total rows for specific report where activity was "VIEW".

Requirements:

I would like to show last 3 months data from current date.

For example; I have this table in primary table; (Please see blue bar highlighted where you can see data from current month till last 3 3 months)

What I want is in any casey whenever user open the report on any specific date of the month then they should see data for last 3 months from that month. In this case, I should see the result like this;

This is how i would like to so top 10 reports which has most views in last 3 months.

Any help will appreciated!

Thanks

[v-yalanwu-msft · Accepted solution]

Hi @damit23183  ,

You could modify your measure by the following formula：

Measure 1:
_date= EOMONTH(CONVERT(MAX('Table'[Month])&" 1",DATETIME),0)

Measure 2:

Total =
CALCULATE (
SUM ( 'Table'[No of Views] ),
FILTER (
'Table',
[_date] <= EOMONTH ( TODAY (), 0 )
&& [_date] >= EOMONTH ( TODAY (), -2 )
)
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi @damit23183  ,

You could modify your measure by the following formula：

Measure 1:
_date= EOMONTH(CONVERT(MAX('Table'[Month])&" 1",DATETIME),0)

Measure 2:

Total =
CALCULATE (
SUM ( 'Table'[No of Views] ),
FILTER (
'Table',
[_date] <= EOMONTH ( TODAY (), 0 )
&& [_date] >= EOMONTH ( TODAY (), -2 )
)
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[damit23183]

Hi,

Thanks for your response.

Qucik question from your solution;

1) Why did you use 1 in Measure 1 because CONVERSION will fail in measure 2 when compare measure 1 with other condition. Here, you have used 1 which has failed to convert when i applied to formula.

Thanks

[v-yalanwu-msft · Accepted solution]

Hi， @damit23183 ；

My application 1 is to convert the text format to the date format, so that it is easier to compare. If you fail, you can see if your value has null values or other characters.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What have you tried?  Did you start with the TODAY() DAX function and then identified the current month and the two previous months?

[damit23183]

Hi,

I used this calculation;

= CALCULATE( [Measure], DATEADD('Date'[Date],-3, Month)

= CALCULATE( [Measure], DATESINPERIOD('Date'[Date],TODAY(),-3, Month)

Both are not working for me. 

Here Measure is Measure that I have created based on requirement and Date is Data Table which is MARK AS DATE table.

THanks