Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jibran
Helper II
Helper II

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 created on that particular

month as shown below. For example on month of June we create 15 quotes want to see that. I did create a date table with data, month name, month number and year and link date to this table but no result. 

 

 Second Table.JPG

 

Here is my working query

 

Measure = CALCULATE(COUNT(Quotes[QUOTEID]),Filter(All(Quotes[DATECREATED]),MONTH(Quotes[DATECREATED]) <= MONTH(MAX(Quotes[DATECREATED]))))

But above code is not working can someone help me on this.

 

Regards

 

Jibran 

2 ACCEPTED SOLUTIONS
v-haibl-msft
Employee
Employee

@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] ) )
    )
)

 

Filter data monthly_1.jpg

 

Best Regards,
Herbert

View solution in original post

@jibran

 

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

 

Filter data monthly_2.jpg

 

Best Regards,
Herbert

View solution in original post

3 REPLIES 3
v-haibl-msft
Employee
Employee

@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] ) )
    )
)

 

Filter data monthly_1.jpg

 

Best Regards,
Herbert

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

@jibran

 

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

 

Filter data monthly_2.jpg

 

Best Regards,
Herbert

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.