Forum Discussion

edavila's avatar
edavila
Icon for Helper II rankHelper II
9 years ago

Amounts not showing Correctly

I have 2 tables one that has all the dates from 2 years listed and also the name of the month, week and year   and I create a report as table to show the Balance sheet for the company at a certain ...
Need Help
Show and Tell
Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

edavila

 

Hi, do you want YTD of Amount 2016?

 

If answer is yes, i propose do some changes:

 

1. Change relationship between FS2016 and DateSH to Both

 

 

2. Create a measure:

 

 

Amount2016YTD =
CALCULATE (
    SUM ( 'FS 2016'[Amount 2016] ),
    FILTER ( ALL ( DateSH ), DateSH[date] <= MAX ( DateSH[date] ) )
)


3. Put this measure in your chart

 

 

 

4. Finally, to order your weeks you should change your WeekSort Column

 

A way to sort is this:

 

Week Sort =
IF (
    DateSH[Week] < 10,
    DateSH[Year] & "-0"
        & DateSH[Week],
    DateSH[Year] & "-"
        & DateSH[Week]
)

 

Like Parry recommend , yo should use only a FS2016-2017 to simplify things later.

  • edavila's avatar
    edavila
    Icon for Helper II rankHelper II
    9 years ago

    Victor/Parry

     

    thank you and let me ask you another question if I combine both tables what Im going to do when I run out of space in the excel sheet it only has like a 1M rows

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    edavila

     

    You can combine in Power BI Desktop.

     

    Import your both tables and go to Edit Query to do this. --Select thr action "Append Queries"

     

     

  • edavila's avatar
    edavila
    Icon for Helper II rankHelper II
    9 years ago

    Victor

     

    to clarify do I need to create a new table and then append both tables and let me tell you that I change the direction of the relationship for FS2016 to both and FS2017 has a single when I try to change it will give and error