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 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

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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      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

  • 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.