Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
5 years ago
Solved

Monthly Trend for CountRows

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 dat...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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's avatar
    v-yalanwu-msft
    5 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
5 years ago

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's avatar
damit23183
Microsoft Employee
5 years ago

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's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    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.