Logic calculation per year

[DanCasSan]

Dear,

I have a matrix structured in the following way: in the rows is the description and amount of each concept and in the columns I have the years.

What I want to calculate is the following: For the years before the current one, you must show me the information for each year, but only for the month of December. In the case of the current year, the information corresponding to the current month must be displayed.

It should be noted that I have 2 filters (Quarter and Month), if these are not filtered, the logic mentioned in the previous paragraph will be fulfilled; If one of these two filters is used, the information presented must obey what is being filtered, for example, if the month of July is filtered, all the data must be showing the information for July in each year.

I appreciate your important support.

Thnx & Reggards,,
DC

[v-stephen-msft]

Hi @DanCasSan ,

The pbix file is here.

1.Create a Month table and the MONTH column is sorted by MonthNo column.

MONTH =
ADDCOLUMNS (
    DISTINCT ( 'Table'[Date].[MonthNo] ),
    "MONTH", FORMAT ( DATE ( 1900, [Date].[MonthNo], 1 ), "MMMM" )
)

2.Create the measure.

Measure =
IF (
    ISFILTERED ( 'MONTH'[Month] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', FORMAT ( [Date], "MMMM" ) = SELECTEDVALUE ( 'MONTH'[Month] ) )
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', MONTH ( [Date] ) = MONTH ( TODAY () ) )
    )
)

3.If the slicer does not select a month, the current month's data will be displayed. If the month in the slicer is filtered, the data of the filtered month is displayed.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @DanCasSan ,

Please consider to create a pbix file that contains sample data, but still reflects the data model (https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/).
You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom

[amitchandak]

@DanCasSan , Not very clear. But try a measure like this with date table and year also coming from date table 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

WHY TI fails https://youtu.be/OBf0rjpp5Hw

[DanCasSan]

Hello @amitchandak ,

Thanks for your prompt response, I have used the formulation you indicate, but the other years disappear and show the year before the current one, as shown in the image:

What is required is that they remain every year, but the sum of the amounts must be only for December, in the case of the current year it must show information for the current month.

Thank you!

Reggards,
DC