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
marcopete
Regular Visitor

Measure for hide months without data

Hi.

 

Can you help me with that? 

 

I have a chart with data from this year and previous year.

 

Attach image.

 

Sin título.png

 

How I can show only the months with data for both years? 

 

My dax for this: MedidaTiempoTotalIndMensual:=CALCULATE(SUM([IND]);FILTER('DIM TIEMPO'; 'DIM TIEMPO'[AÑO] >= YEAR(NOW()) - 1))

 

In this case until march without explicit filter.

 

I appreciate your help.

 

Regards.

 

1 ACCEPTED SOLUTION

Thanks everyone.

 

This dax show me until i have the data, not based in data but in time and I resolved.

 

CALCULATE(DIVIDE([Data1];[Data2]);FILTER('DIM TIEMPO';'DIM TIEMPO'[AÑO] >= YEAR(NOW()) -1);'DIM TIEMPO'[NUMERO DE MES] < MONTH(NOW()))

 

Thanks again and all your replies are in my knowledge.

View solution in original post

6 REPLIES 6
CheenuSing
Community Champion
Community Champion

@marcopete

 

1. I am assuming you have a date table with no gaps. 

2. I am also assuming you have a MonthNumber column in the Date table which is Month(Datetable[DateField])

3. Create a Measure called CurYrLastMonthNumber = Month(Lastdate(Sales[Date]))

     This will find the lastdate of from the sales table and get the corresponding Month Number for it.

 

4. Create a column in Date table called Show as

      ShowMonthsUpto = IF (
                     [MonthNumber] <= [CurYrLastMonthNumber] , 1, 0

                                )

5. Show will  have a value of 0 or 1 in the date table.

6. Assume you have a measure called [Sales] that calculates the sum of Sales . As per your requirment I am assuming your sales records contains future predicted sales.

7. Create a bar chart  where x-axis is Month and Y- axis is Sales measure.

8. In the visual level filter for the bar chart drag the field ShowMonthsUpto and set the filter condition show items when the value is 1.

9. You will then be able to see the Sales for the same period for both years. Months after the lastmonth in the sales for previous year will not be shown.

10. The above solution assumes you will always be showing for the current year and previous in the chart / report. 

 

Should you require further assistance , please do not hesitate to reply to this post.

 

If this works for you please accept it as a solution and also give kudos.

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thanks everyone.

 

This dax show me until i have the data, not based in data but in time and I resolved.

 

CALCULATE(DIVIDE([Data1];[Data2]);FILTER('DIM TIEMPO';'DIM TIEMPO'[AÑO] >= YEAR(NOW()) -1);'DIM TIEMPO'[NUMERO DE MES] < MONTH(NOW()))

 

Thanks again and all your replies are in my knowledge.

Thanks everyone.

 

This dax show me until i have the data, not based in data but in time and I resolved.

 

CALCULATE(DIVIDE([Data1];[Data2]);FILTER('DIM TIEMPO';'DIM TIEMPO'[AÑO] >= YEAR(NOW()) -1);'DIM TIEMPO'[NUMERO DE MES] < MONTH(NOW()))

 

Thanks everyone again.

kdejonge
Employee
Employee

 

can you share the data? I am not completly clear how your measure works.

Sean
Community Champion
Community Champion

Hi @marcopete We can do this by creating 2 Calculated COLUMNS in the Calendar Table

 

You first need to find the Last Transaction date (if you already have a Measure use the same formula)

But create a COLUMN in your calendar table

 

Then create a Show Month COLUMN (if your month column is text use version 1 if number use version 2) 

 

Last Transaction COLUMN = CALCULATE ( LASTDATE ( TableName[Transaction Date] ), ALL ( TableName ) )

Show Month COLUMN =
IF (
    MONTH ( 'Calendar'[Date] ) <= MONTH ( 'Calendar'[Last Transaction COLUMN] ),
    'Calendar'[Mo Num],
    "Don't Show"
)

Show Month COLUMN 2 =
IF (
    MONTH ( 'Calendar'[Date] ) <= MONTH ( 'Calendar'[Last Transaction COLUMN] ),
    'Calendar'[Month Number],
    100
)

 

Then you can use the Visual Level Filter either Show Month Column is not Don't Show OR Show Month Column 2 is not 100

As in the picture

 

Show Only Active in CY Months.png

Thanks, I'll try what you say.

 

Just to know... it's possible with my actual dax? I'm trying with ISBLANK and FILTER commands.

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.