Help Needed - DAX Measure for Financial Summary Visualization

[Anonymous]

Hi PBI Community

I'm new to PBI, not very comfortable with advanced DAX measures, hence asking for help. Example show as below,

I want to display the list of values in my new summary report. Data coming from two tables actual (data source - SQL) and budget (excel). I've created a dummy screenshot for better understanding. Please don't follow the values, it is just for explanation purposes.

1. Picture - My final vizualisation metrics

This is the ultimate view i'm looking for

2. Picture of database

Database model

3. This is what I've achieved so far. I've unpivoted the budget data and made some model changes, nothing producing me the final outcome closer to Picture 1. I've created some measures but it is not showing me by week value rather it is summing up, something like this. Again not in an exact format what I'm looking for. The column highlighted in Red is not showing me with the correct figure, maybe my DAX formula needs some tweak. My aim is to attain the yellow figure which gives me the correct value by week

Week to Date Sales DAX Measure = [

var CurrentDate=LASTDATE(DATES[Date])

var DayNumberOfWeek=WEEKDAY(LASTDATE(DATES[DATE]),3)

return

CALCULATE(

SUM(Budget_Volume[Quantity]),

DATESBETWEEN(

DATES[Date],

DATEADD(

CurrentDate,

-1*DayNumberOfWeek,

DAY),

CurrentDate))

Ultimately, my objective is to create a summary report with actual, budget and previous year figures to be filtered by week number displayed next to a week or by the filter. Also Cumulative and Cumulative Variances in different columns a shown in picture 1.

I appreciate your help in assisting me with the custom visualization

Regards

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490