Showing 12 months of data

[dataSeeker]

I have a Number (Gallons) that i want to divide by 12 and then show the annualized number in a visual by month. The starting month will change.   Is there a way to do this ?

[LivioLanzo]

@dataSeeker 

probably yes, but we need more info than that :). Maybe a data sample along with expected results?

[dataSeeker]

Something like this

Data Sample

Gallons  Target  Annualized Gallons

12000      4/1/19     1000

24000       5/1/19     2000

6000         8/1/19      500

Visual:

Month  Gallon Potential

 April         1000

May           3000

June            3000

July             3000

August        3500

...

April 2020     3500

May               2500

June                 500

[v-yulgu-msft · Accepted solution]

Hi @dataSeeker,

In source table, create a calculated column.

Annualized Gallons = Data_2[Gallons]/12

Then, new a calculated table.

Result table =
VAR datedim =
    FILTER (
        CALENDAR ( DATE ( 2019, 4, 1 ), DATE ( 2020, 6, 1 ) ),
        DAY ( [Date] ) = 1
    )
VAR crossjoitablen =
    FILTER (
        CROSSJOIN ( Data_2, datedim ),
        [Date] >= Data_2[Target]
            && YEAR ( [Date] ) = YEAR ( Data_2[Target] )
    )
RETURN
    GROUPBY (
        crossjoitablen,
        [Date],
        "Gallon Potential", SUMX ( CURRENTGROUP (), [Annualized Gallons] )
    )

Best regards,

Yuliana Gu