how to calculate accumalated data when data for one of argument doesn't exist (2)

[Ewa_28]

Hi everyone,

Please help me! It is important for me.

Please see the below link with my previous post.

how to calculate accumalated data when data for on... - Microsoft Fabric Community

I will be glad.

[Ewa_28]

Hi,

I need to show accumulated data and also percentage of sales (sales vs plan). I created some measures but they don't work when I use filter by regions (eg. WSC) and quarter (eg. 3). Below I also coped pictures.

I also prepare data and pbix. I realy hope that it help you to understand what I want. Please see my data. 

Below you find the link to my files:

https://drive.google.com/drive/folders/1cFdd8LpSFqGl1oomDpbP2pIc6Ud4syEQ?usp=sharing

I hope that it's help you to understand my case.

Regards, Ewa

[Syndicate_Admin]

Looks like your Calendar table is not connected to the data model.

In your fact tables find a column that can be tied to the calendar (for example first day of the week).

[Ewa_28]

hello @Irwan @rajendraongole1 @Ritaf1983 @lbendlin @Greg_Deckler

I would like to ask you for your help.

Below you find my 1-st post regarding the topic. 

https://community.fabric.microsoft.com/t5/Desktop/how-to-calculate-accumalated-data-when-data-for-on...

I prepared sample data: plik excel with pbix. Please see the below link and let me know you see or not.

https://drive.google.com/drive/folders/1cFdd8LpSFqGl1oomDpbP2pIc6Ud4syEQ?usp=sharing

Please see and help me to solve these problems.

thanks in advance

Ewa

[Syndicate_Admin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Ewa_28]

Hi @Irwan 

Ok. I will try to discribe my issue.

I want to show accumulated data for total country and as well for regions. Please below graphs.

For below graphs I use measure: 

Week Wykon =

Var_select_week = MAX('calendar_lista'[Week_number])

VAR_select_kwartal = MAX('calendar_lista'[Q_number])

VAR_select_year = MAX('calendar_lista'[Year_number])

VAR_cur_week = MAX(''Sales'[Week]])

VAR_cur_kwartal = MAX('Sales'[Q])

VAR_cur_year = MAX('Sales'[Year])

VARResult =

IF(

    _cur_year = _select_year

    && _cur_kwartal = _select_kwartal

    && _cur_week <= _select_week,

    CALCULATE(

        SUM('Sales'[Sales]),

        'Sales'[Week]  <= _cur_week,

        'Sales'[Q]  = _cur_kwartal,

        'Sales'[Year = _cur_year)

)

RETURN

Result

legend:

1. it works because I don't use filters by arguments (1, 2a, 2b) - data for total country

2.  it works because I use filters by arguments (1, 2a, 2b) - data for total country

3. it works because I don't use filters by arguments (1, 2a, 2b) - data for one of regions

4. it dosen't work because I use filters by arguments (1, 2a, 2b) - data for one of regions

in my data I have the custom calendar. 
in my x axis I use weeks from table Sales, not from calendar_lista (because it doesn't work, I 

To summarize: yes, I want to show accumalated data per week for regions when data for argument (1, 2a, 2b) doesn't exist. I don't know how to do it 😞 

Please let me know, if it is clear now. 

[Irwan]

hello @Ewa_28 

sorry, i missed this post.

could you share your pbix? it is hard to tell what is the problem from your description.

Thank you.

[Irwan]

hello @Ewa_28 

i dont get the aformentioned issue, maybe i misunderstood the problem but your calculation looks fine, no?

it filters Region, Argument, and Date.

do you want to show accumulated value in week 42 for 2A as 2A doesnt have value in that week?

otherwise please describe more about what is the problem?

Thank you.