one Filter date - balance to return figures from date choose and charts to show 1 yr ago up to date

[Denismc]

Hi,

Is is possible to have 1 filter date drop down to select balance in cards visual latest balance from date choosen and charts to show 1 yr ago up to date for trending?

I can't figure it out, any ideas.

Thanks

[amitchandak]

@Denismc , You can use time intelligence and date table and have the 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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.current and prior measure

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

[Denismc]

Hi,

Thanks for the quick reply. Not what I'm looking for. 

I have one date slicer linked to get figures on 31st May 2020

then a second slicer between date filter for 1st june 2019 to 31st May 2020 to filter the charts. 

I'd like one filter for flitering all visuals. 

Is that possible. 

Thanks

Denis

[vanessafvg]

are these both from the same data column? or are they from the same table or separate tables?

[Denismc]

Hi,

Same column and I only have 1 table

thanks

[vanessafvg]

ok i am little confused then, firstly what is your objective with the data, what are you trying to do with 2 filters specifically, why do you need to filter on 2 separate filters, why can't you just filter from may - june with a range?

[Denismc]

Hi,

Hope this makes sense. 

I have a table for example below, I want to show in a card with the month end closing balance e.g. May 29,535 and then a chart showing the trend for each month. This is a simple example, but I have 23 pages of a report using this logic. 

Date                                          Closing Balance

31/01/2020                                 4,356

29/02/2020                                10,565

31/03/2020                                15,856

30/04/2020                                 21,235

31/05/2020                                29,535

15/06/2020                                 33,252

[v-xuding-msft]

Hi @Denismc ,

You coould use a relative date slicer to filter. Then click it and select the no impact icon for the chart which shows one year trend.

Reference:

Creating a relative date slicer and filter in Power BI 

Change how visuals interact in a Power BI report 

Use the Analytics pane in Power BI Desktop 

[Denismc]

Thanks for the replies, but this is a 23 page, I wnat to make it easy as possible. 

I created a column with the following, but instead of max(ExtractPortfolio[EffectiveDate]), I want to use a selectedvalue, which you can only use in a measue (I believe), so i can use a dropdown to select 1 month end date and the charts can use the formula for which dates to trend the data back to in the past. 

12 Month Chart Filter =

var maxdate = max(ExtractPortfolio[EffectiveDate])

return

IF(DATEDIFF(ExtractPortfolio[EffectiveDate], maxdate, MONTH) IN GENERATESERIES(1,12,1),1,0)

 Thanks