Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculation made based on user screen selection (date)

Hi, I’m new to power bi, I been trying to solve this but don’t have enough knowledge of DAX to solve this or I might be complicating things ☹

 

I need to create a dynamic report, where the users can select the year, quarter or month for which they want to produce the report, I was able to setup something that work when year level is selected, but because of the way I did it, it doesn’t work when users select quarter or month and I have no clue how to do this, can someone please help

 

I have created a date table and created the joint in the data model, I use the dates from the date table

Everything shown in the report should be based on the year, quarter or month selected by the user on screen

 

Here is a sample of data : 

 

Let say that the user selected year 2021, quarter 1 and january in dropdown

 

Calculate total amount of payments made in 2021 for loan awarded before 2021

       At year level = 161800$

Calculate total amount of payments made in each quarter in 2021 for loan awarded before 2021

       At Quarter level (2021 quarter 1) = 51800$

Calculate total amount of payments made in each month in 2021 for loan awarded before 2021

       At Month level (2021 january) = 15000$

 

Here is what I have done for year level, it’s working, but how can I make this work for selection at the quarter level and month level using the date selected by the user in screen ?

 

Payment selected year/awarded before selected year =

CALCULATE(SUM(Financement[Payment_1__c]),

FILTER(ALL(DimDateAutorisee[Year]),DimDateAutorisee[Year]<MAX(DimDateAutorisee[Year])),

FILTER(ALL(DimDateDeboursee[Year]),DimDateDeboursee[Year]=MAX(DimDateAutorisee[Year])))

 

Thank you

1 Reply

  • Anonymous  Hi, you can try below;

     

    CALCULATE(SUM(Financement[Payment_1__c]),
                        KEEPFILTERS(DimDateAutorisee[date] < DATE(2021,1,1))
    )

    Hope this helps. Thanks