Forum Discussion

jibran's avatar
jibran
Helper II
9 years ago
Solved

Filter data monthly

Hi All, I am working on to show data on monthly basis but struggling to create a DAX function. I have a date created column that is in this format 01/01/2017 and I want to see how many quotes been ...
Dashboards
Reports
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    jibran

     

    Do you want to get the total quote amount for each month? If yes, after create relationship with the calendar table, you should be able to show the result with a Table visual like below.

     

    If you want to get the running total, you can create a measure with following formula.

     

    Running_Total_Month = 
CALCULATE (
    DISTINCTCOUNT ( Table1[Quote No] ),
    FILTER (
        ALL ( Table1 ),
        MONTH ( Table1[Date Created] ) <= MONTH ( MAX ( Table1[Date Created] ) )
    )
)

     

     

    Best Regards,
    Herbert

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    jibran

     

    Just remove the Running_Total meansure from the Table visual should be OK. 

     

     

    Best Regards,
    Herbert

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

jibran

 

Do you want to get the total quote amount for each month? If yes, after create relationship with the calendar table, you should be able to show the result with a Table visual like below.

 

If you want to get the running total, you can create a measure with following formula.

 

Running_Total_Month = 
CALCULATE (
    DISTINCTCOUNT ( Table1[Quote No] ),
    FILTER (
        ALL ( Table1 ),
        MONTH ( Table1[Date Created] ) <= MONTH ( MAX ( Table1[Date Created] ) )
    )
)

 

 

Best Regards,
Herbert

  • jibran's avatar
    jibran
    Helper II
    9 years ago

    Hi Herbert,

    How can I see just the total number of quotes raised each month.

    Becuase this solution adds the total number of quotes to the next month and so.

    Our aim to show total number of quotes raised per month.


    Can you please help on this?

     

    Thanks for your help in advance

     

    Many Thanks 

     

    Jibran Ishtiaq

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      jibran

       

      Just remove the Running_Total meansure from the Table visual should be OK. 

       

       

      Best Regards,
      Herbert