Forum Discussion

MikeBresnan's avatar
MikeBresnan
Icon for Helper I rankHelper I
2 years ago

Qtrly trend visualization for data with single date slicer

I'm trying to create a report that displays 1 single week of cash balances, but also has a trend graph of the prior weeks in the existing quarter, alongside the same quarter in the previous year. I can't figure out a measure (is that the correct method here?) that will provide the current quarter's information, let alone the full quarter from the previous month. below is the graph i'm trying to recreate:

(this was created in excel by manually dropping in the balances into the appropriate buckets for each series.)

 

one other issue is that the data is broken up into multiple accounts that roll up to a company. i tried to use calculate to sum the data, but it was totaling up the previous weeks. I tried to do a MAX function but it was returning the largest account value under that company, instead of the combination of all the accounts under that company. 

 

I attached a pbix example below, with similar data. is there a way to get a look at quarterly data based on a single date in the slicer? Do i need to break up the single week and full quarter visualizations? it seemed like if i selected the quarter, i got a bit further with the trend graph, but blew up the single week viz.

 

https://drive.google.com/file/d/1AUuzkQXyKdJ-NQ37mKWme6Lol4ZeHavb/view?usp=sharing

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    MikeBresnan , With help from date table joined with date of your table , you can use year behind measure of week year behind meausre

     

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

     

    You can also try month behind

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

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s